Recently my team requested to provide scripts of all objects in a database to create on another instance, so I used ‘Generate and Publish Scripts Wizard’ and created all objects on another database instance. Today we are going to see how we can generate scripts with SQL Server Management Studio. We will take example of AdventureWorks2012 database here.
First go to Object Explorer and right click on AdventureWorks2012 database then go to Tasks and click Generate Scripts… [refer Figure-1]
Figure 1: Generate Script Path
It will open ‘Generate and Publish Scripts’ wizard where you will find introduction about different steps to perform in this wizard which will guide you to generate scripts. [refer Figure-2]
Figure 2: Introduction to Generate Scripts
Basically it is 4 steps process as given below.
Step 1:
You have to select database objects to generate scripts.
You have to select database objects to generate scripts.
Step 2:
You have to specify scripting or publishing options.
You have to specify scripting or publishing options.
Step 3:
Review and confirm your selected objects.
Review and confirm your selected objects.
Step 4:
Now you can finally Generate scripts and save it.
Now you can finally Generate scripts and save it.
Once you finished introduction part then click Next to continue. Now you can select database objects to generate scripts. You have option to select for entire database or specific objects in database. [refer Figure-3]
Figure 3: Choose Database Objects
Following are available objects in this wizard which you can use to generate scripts:
– Tables
– Views
– Stored Procedures
– User-Defined Functions
– Users
– Schemas
– Views
– Stored Procedures
– User-Defined Functions
– Users
– Schemas
After choosing required objects you can continue wizard with Next button.
Now you can select Output Type as ‘Save scripts to a specific location’ and specify location and file name. You also have options like to generate all objects in a single file or generate one object in one file and specify your location and file name then click Advanced button and choose required settings and click Next. [refer Figure-4]
Figure 4: Set Scripting Options
In summary window you can review your selected source, target and options and click Nextbutton. [refer Figure-5]
Figure 5: Summary to Review Selection
Finally it will generate database scripts on the specified location which you can create on other instance easily. [refer Figure-6]
Figure 6: Save Scripts
You can click on Finish button to close the generate scripts wizard.
CLICK HERE to watch live practical.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
