Thursday, December 23, 2010

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)

2 comments:

  1. yeah, object dependency error usually occurs specially when we create whole database script. But i like to manually correct these errors, if number of these errors are not more then my expectations.

    ReplyDelete
  2. 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 sequence
    thanks anyway

    ReplyDelete

All suggestions are welcome