Import and Export SQL database to *.sql script via SQL Management Studio

AUTHOR: Peter Milchev

DATE POSTED: July 05, 2018

Import/Export SQL database to *.sql script

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.

I. Export database to *.sql script

1. Right-click on the database that should be exported.
2. "Tasks" -> "Generate Scripts..." - A Generate and Publish Scripts popup should appear
   
   

    

   

 

 

3. On the "Introduction" screen click the "Next >" button - "Choose Objects" screen will appear;
   

    

   
   

 

 

4. 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; 
   

    

   

 

5. 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 Options popup, you can set "Types of data to script" to "Schema and data". That would export the database schema along with the data.

      

     

 

6. On the "Summary" screen the options you have previously selected and if you are satisfied with them, click the "Next >" button.
   

    

   
   

 

7. The database should be exported and you can find the generated *.sql script in the location you have set in step 5.
   

    

   

 

 

II. Import database from *.sql script

1. Open the *.sql script with Microsoft SQL Server Management Studio
2. 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.
   

    

   

 

3. 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

 

4. Click the "! Execute" button, or use the F5 shortcut for it.
   

    

   

 

5. After a successful execution of the Query, you should find your newly populated database.
   

    

   
   
   

Related resources:

• docs.microsoft.com: How to: Generate a Script (SQL Server Management Studio)