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)