Generate Scripts – How to script database objects and data using Management Studio (SSMS)

Sql Server Management Studio provides this a very useful tool to script all database objects into SQL scripts including Data, Indexes, Triggers, Constraints, Logins and Synonyms.

Open SSMS >> Go to a user database >> Right Click >> Tasks >> Generate Scripts…

Menu Navigation for “Generate Scripts” in SSMS

This is a very useful tool with many customization options. It allows you to script definitions of:

1. Tables
2. Views
3. Stored Procedures
4. User-Defined Functions
5. Users
6. Database Roles
7. Synonyms
8. Schemas

And if you go to “Advanced” options we can enable:

1. Database Creation
2. Indexes
3. Statistics
4. Triggers
5. Primary Key, Foreign Key and Unique Constraints
6. Object Level Permissions
7. Data
9. … and many more (just check out the tool, its a valuable tool)

For this post, I will try to script out a sample database (DBADB) and some of its objects.

Step 1: When you open the tool from the path above this is the first (standard) welcome screen:

Welcome Screen – Generate Scripts

Click Next to see the list of objects types that could be selected.

Step 2: Choose Objects screen allows you to

• Script entire database and all database objects
• Select specific database objects
  • Tables
  • Views
  • Schemas
  • …
  • (please note that this is a dynamic list based on the types of database objects available in the selected database )
  

  Select Objects – Generate Scripts

Step 3: “Set Scripting Options” allows you to specify the location to save the script file and its properties. If you want the file in Unicode or ANSI text. One important point that must be mentioned here is the option to scripts each object into its own script file. This is of great value in certain situations. Also notice the “Advanced” button on the right. That allows you to set more objects that are not listed in the Step 2 screen “Choose Objects”. These are objects like Indexes, Triggers, Statistics, Data, etc. (more listed in the top portion of this post)

Specify Options – Generate Scripts

Step 4: “Advanced Options” menu opens when you click on the “Advanced” button in Step 3. This is deserves a special mention as it has a vast number of options available. As seen in the image below, you can set:

• “DROP and CREATE” for each object
• Script Data for tables
• Defaults
• Logins
• Users
• …
• and many more.

Advanced Options – Generate Scripts

Step 5: “Summary” screen show all the options you selected in the previous steps and give you a chance to review and make any changes.

Review the options – Generate Scripts

Steps 6: “Save or Publish Scripts” screen is where you see the progress. First it gathers a list of all the objects you selected and lists them on the screen and then gathers definition of each objects. You can see the progress as it completes each object.

Progress Report – Generate Scripts

On Success, this is what the final screen looks like.

Note: In the “Advanced” options, you can set a flag that allows the process to continue when any errors are encountered. If for some reason an object could not be scripted, it just moves on to the next object in the list  (like in SSIS)

Hope this helps,

SqlTimes

Read Full Post »