Exporting database using SQL server management studio

Posted on December 29, 2017 by Suresh Kamrushi in General

Exporting database from SQL Management studio is very easy and it provides various options to make developer life easy, like:
1) Export the whole database into a single file or single files per project.
2) Export database schema or table data only or both.
3) Export with backward compatibility. You can export database for server version 2005, 2008, 2012 or 2014 from the current version.

Here I am sharing step by step process, how you can export data using Microsoft SQL Server Management Studio(Version 17.3):
1) Right click on database and click on “Task” then look for “Generate Scripts…”
SQL dump generate Scripts
2) Ignore the first window of “Introduction” and click next.
3) On “Choose Object” you can select specific objects for exporting. For now you can leave it default and click on next.
SQL dump Select assets
4) In “Set Scripting Option” window you can select, single file or multiple files per object for your export. If you are selecting single file then you need to specify the file in “File name” or if you choose “single file per object” then you need to select the directory where you need to store all files that are being exported.
SQL dump Publish or save
5) If you click “Advance” button you will get more options for exporting.  Out of all options two options are worth mentioning:
a) You can specify server version for which you are generating the script. Management studio will take care of the rest.
b) And type of data that is going to export i.e. Schema, data or both.
See the screen shot below:
SQL dump Advance option
5) Summary tab is final one, which provide all parameter you have choosen like target files(s) or any other option you have selected before you start exporting. Once everything seems fine you can click next button.
SQL dump Review selection
5) Save and publish script will show the status of data export. It will take a while to generate all scripts.
SQL dump Publishing
5) Once it is done, “Finish” Button will be enable. Click “Finish” to close the window and complete the process.
SQL dump Finish

Hope it will help someone!!!

  Comments or questions are welcome