Sql Server: Sequence to Create Error Free Objects Script for Whole Database


While executing whole database script, how to avoid object dependency errors???
 Easiest way to create script for whole database is as follow:
     I.        Login to Sql Server Management Studio
     II.       Right click on your desired database — > Tasks —  > Generate Scripts
     III.      Select your desired database and on bottom click on check box with caption “Script all objects in the selected database”
     IV.        Click next and then finish to view resultant script.
But for production database, resultant script, created through above mentioned method can generate DEPENDENCY errors. Although you had selected “Generate script for dependent objects” on “Choose Script Options” page of script wizard.
To avoid such problems, I had adopted following method.
·         To avoid dependency errors and to have smaller size of resultant sql files, I like to create script in chunks with following steps.
                                     I.        In first part create script for all tables, keys, triggers and indexes. But never forget to select yes for “Generate script for dependent objects”
                                   II.        Generate script and save for all views, No need to create script for dependent objects.
                                  III.        Generate script for all functions, No need to create script for dependent objects
                                  IV.        Generate script for all store procedures , No need to create script for dependent objects
On Target database scripts must be executed with following sequence:
1.   Tables (with keys, triggers and indexes) Script
2.   Functions script
3.   Views script
4.   Store Procedures script
5.   Encryption Keys Script (If encrypting data)
6.   Data Script (To insert data. How to create data script click here)
Advertisements

Posted on December 23, 2010, in SSMS. Bookmark the permalink. 1 Comment.

  1. This is not working. I tried it and it did not work.My views depend on each other so I need a solution that generates a script for the views in the right sequencethanks anyway

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: