Import and Export SQL database to *.sql script via SQL Management Studio
This article will show you how to export a Database to a *.sql script and the how to import it from such kind of scripts.
Export database to *.sql script
Right-click on the database that should be exported.
-
"Tasks" -> "Generate Scripts..." - A Generate and Publish Scripts popup should appear
-
On the "Introduction" screen click the "Next >" button - "Choose Objects" screen will appear
-
On the "Choose Objects" screen you can choose whether to export the whole database or only some of the tables. Choose the desired objects and click "Next >" - "Set Scripting Options" screen should appear
-
On the "Set Scripting Options" you can choose whether to save or publish the script. Once you customize all the desired save and advanced options, click the "Next >" button.
- Clicking the "Advanced" button would allow you to customize the exported content.
Pro tip: From the Advanced Scripting Optionspopup, you can set "Types of data to script" to "Schema and data". That would export the database schema along with the data.
-
On the "Summary" screen the options you have previously selected and if you are satisfied with them, click the "Next >" button.
-
The database should be exported and you can find the generated *.sql script in the location you have set in step 5.
Import database from *.sql script
Open the *.sql script with Microsoft SQL Server Management Studio
-
Create a new database with the name of your choice via right-clicking the Databases folder and choosing "New Database...". Then you choose the name for the database and optionally further configure the database settings.
-
In the *.sql script, the first two rows should be something similar to
USE [DatabaseName] GO
Here in the brackets, you can use the name of the database you would like to populate.
USE [YourDatabaseName] GO
-
Click the "! Execute" button, or use the F5 shortcut for it.
-
After a successful execution of the Query, you should find your newly populated database.