In this article, I am going to demonstrate how to generate MS SQL database script along with data using SQL Server Management Studio 2008:
Step 1:
First of all open your SQL Server Management Studio 2008 :
To Open: Go to Programs > Microsoft SQL Server 2008 > SQL Server Management Studio Express
Now, connect to a MS SQL Server 2008 database instance using the Server Explorer or using the Connect screen.
Then open the Object Explorer window and expand the Databases node.
In my case i have connected to the local SQL Server 2008 instance (SQLEXPRESS) and clicked on the Databases node and a list of all the existing SQL server databases will be visible in the object explorer window .
Step 2:
Now, Select your database for which you want to generate your sql script with data. In my case I selected the ms sql database "Auto" which includes all the tables, stored procedures, data etc for which I want to generate script.
Step 3:
Now, right click on your database that you selected and follow this path
Right click on your database > Taks > Generate Scripts
Step 4:
Now when you will click on "Generate Scripts" from the previous step, you will get a window saying "Generate SQL Server Scripts Wizard"
Click on "Next"
Step 5:
Now, select the database from the list that you want to script and press "Next" like this:
Step 6:
You will get script options, here is the place where you can configure the script details:
In the "Script for Server Version" select the version of sql database for which you are generating this script, in my case i am doing it for 2008 version.
Now set "Script Data option" to "True" because we are generating script that will include data also:
Step 7:
Choose Object Types
Here, click on "Select All" and press "Next"
Step 8:
If you get "Choose Database Roles" option, click on "Select All" and press "Next"
Step 9:
If you get "Choose Schemas" option, click on "Select All" and press "Next"
Step 10:
If you get "Choose Stored Procedures" option, click on "Select All" and press "Next"
Step 11:
Now, you will get "Choose Tables" option, check tables that you want to include in your script. In my case i am including all tables so click on "Select All" and press "Next"
Step 12:
If you get "Choose Views" option, click on "Select All" and press "Next"
Step 13:
Now you will get "Output Option" screen in the Generate Script Wizard where you can select where you want to save the option .sql script of your database. Locate the output location and press "Next"
Step 14:
You will get "Script Wizard Summary" - this is the last stop before proceeding to the script generation where you can see which options you have selected for generating your database script. It is something like Reviewing your selected options.
Step 15:
Finally you will see Generate Script Progress screen that displays the status of the scripting operation. If any error, occurs, you can view the error details on this window.
If everything runs without any error and does not fail, you will see Success status:
That's it, your database script is ready. It should look something like this:
