Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Migrating Database Objects

By Jeffrey Yao,

Database Objects Migration


Recently, I "dug out" two laws to be a happy DBA, so I cannot wait to share with all DBA's here to hear your feedback. :-)

DBA Law One: The amount of fun from a DBA job is directly proportional to how much you can make simple work complex and complex work simple.

DBA Law Two: The amount of leisure time you can enjoy is proportional to the percentage of the collective team work you can leverage.
(note: I wish to see more laws contributed here by other DBA's)

The following example illustrates how I use the two laws as my guidelines to tackle a real world issue.

To me, database migration is no fun, esp. when I have to migrate a few hundred objects out of several thousand objects in different databases created by a bunch of teams through many months of work. What? You say backup and restore? No, if it were that simple, I would be laughing in my dream as I would have more time to surf online. For example, with data warehousing projects becoming more popular these days, more than often, you will find new projects are created based on the previous work, which means you only add new dimension tables, views, stored procedures in the old database developed in a project before last. What cause you to scratch your head is that the tables and views and their relationships may not be created by you, but by other developers / DBA's in various project teams. Now it is your task to migrate several projects to the UAT (User Acceptance Test) environment from several unit test environments and to production environment when the UAT is done.

Generally speaking, there are three ways I can choose to do the migration, each has its advantages and disadvantages.

Method Advantage Disadvantage
Gather the original scripts that create the tables in the test environment, massage them to make it work as a whole for generating all needed tables Easy to collect scripts, if SP and views are encrypted, this may be the ideal way to choose. Pretty heavy work because you have to combine the DRI scripts by copying & pasting, shuffling or modifying the scripts wherever necessary.
Some objects, esp. jobs, are rarely created using scripts, so you still have to generate the script for these objects.
Use EM by right-clicking the table and generate the script for all database objects Easy to learn by anyone Boring and no fun, you learn nothing from what you have done
Use DTS Copy DB object task GUI interface; easy to maintain Not flexible sometime, for example, the migration of the product will be done by a client DBA, who prefers pure scripts.
Jobs cannot be migrated this way
It is a pain to pick a few hundred objects out of thousands of objects from various databases.
Script the all the objects with VBScript and DMO according to self-defined migration rules. Most flexible; easy to share;
You can define your protocol on how to script the objects and leverage your team work, and make the labor-intensive work more enjoyable
If SP's and views are encrypted, no way to generate the scripts with this approach

According to DBA Law One, I need to choose an approach that makes the labor-intensive and complex work simple like a breeze to get the fun.
According to DBA Law Two, I need to find a way to leverage as much work as possible done by others to save for my "leisure" work time.
Combining these two criteria together, I decide to choose the last approach mentioned above.

Features of the migration script package

The approach actually contains two key component, one is a Excel sheet file and another is a VBScript program. The technical details will be outlined later in part 3. My designed feature for the migration package is as follows:

(1) The package can drop the created database objects when necessary, so if there is error occurring after you created 300 objects while there are still 400 left, you do not have to manually delete the 300 created objects.

(2) The package can migrate objects to different databases automatically.

(3) The package should be well self-documented, which means the migration scripts can be easily read and understood.

To achieve the goals mentioned above, I created an Excel sheet file to define my own migration protocol.

Server DB Object Type Note
Svr_Dev_1 Pubs Authors T Jeff Apr.1, 2005
Svr_Dev_2 NorthWind Invoices V Key view

Migration Assumption:

(1) Objects from the source databases will be migrated to the target databases with same names, i.e. from "pubs" (source) --> "pubs" (target)
(2) Database objects are only migrate to ONE target server each time.

Migration Protocol Definition:

(1) [Server] column: Source server where source databases are located.

(2) [Database] column: Source Databases

(3) [Object] column: The name of the object you are going to migrate

(4) [Type] column: T = Table, V = View, SP = Stored Procedure, J = Job, UDF = User Defined Function ...
(note: DTS package cannot be migrated with this approach because DTS package cannot be scripted)

(5) [Note] column: Comment you want to add to your script for that specific object, such as who created the object when etc. (I'll explain more later)

Actually, we can add more columns to define our specific needs, for example, we can add another column [Initialization], which we want to add some codes for initializing the newly created tables by inserting some records; or we can add a new column [NewName], which will change the newly created objects to new names, or we can add a column [TargetDB], which will migrate the object to the targeted db (if this is the case, the assumption 1 mentioned above can be omitted)


Step 1. Load the data in the Excel file (or CSV) file into a table (say MigrateObject) in a temporary database. (Actually, I always create a database [Maint] for my personal use, such as logging my own events, my work log etc. ). The easy and straightforward way is to use a DTS package to finish the work, of course, bcp and bulk load can also be used.

Step 2. Create a VBScript program, which I called "Migration Prep Engine", to loop through the MigrateObject table and generate the SQL scripts for migration.

Some explanations:

(1) A table is scripted into two parts, one is the basic table structure creation script together with other keys (PK, Unique keys etc) but not foreign keys. The other part is to create a script that will generate table FK's. The reason is that we do not need to care about the table creation sequence by doing this way. Otherwise, we have to create parent tables before children tables can be created, which refer to the parent tables.
(If a table has triggers, we need to create a third part dedicated to the triggers for the table)

(2) The script does not consider security content around database objects. My experience is usually security in one environment (test or development) is not the same as in another environment (production). Usually, a DBA will define, create and modify the security policy manually (such as add or delete one login or modify a role etc)

(3) This script excerpt servers as an example, I only consider scripting two objects, one is table (without trigger) and another is view. For all other objects, only a few minor changes are needed.

Rem MigrateObject.vbs is used together with the Excel sheet file
MigrateObject.xls, which
Rem is loaded into the table Maint.dbo.MigrateObject table
Rem Created: Jeffrey Yao Dec.12, 2004

option explicit dim oRS, oCnn, sqlCmd, strCnn dim DB_Old_T, DB_New_T, DB_Old_V, DB_New_V dim blnWriteUseDB ' control whether to write a "Use <DB> Go" the script Const SQLDMOScript_DRI_PrimaryKey = 268435456 Const SQLDMOScript_DRI_ForeignKeys = 134217728 Const SQLDMOScript_OwnerQualify = 262144 Const SQLDMOScript_IncludeHeaders = 131072 Const SQLDMOScript_NoDRI = 512 Const SQLDMOScript_AppendToFile = 256 Const SQLDMOScript_Default = 4 Const SQLDMOScript_Drops = 1 Const SQLDMOScript_ToFileOnly = 64 Const ForAppending = 8 set oCnn = CreateObject("ADODB.Connection") 'assume we use the stand SQL Server authentication method strCnn = "Provider=sqloledb; data source = MyServer; initial catalog = Maint; user id=jeffrey.yao; password = jy" strCnn set oRS = CreateObject("ADODB.Recordset") sqlCmd = "select server, db, object, type, note from MigrateObject " 'order by server, db, type set oRS = oCnn.execute (sqlCmd) DB_Old_T = "" DB_Old_V = "" blnWriteUseDB = 0 blnWriteUseDB = 0 '0=Not write "USE <DB> GO" in the script, 1=Write if not oRS is nothing then do while not oRS.eof DB_New_T = cstr(oRS.fields("DB").value) DB_New_V = DB_New_T if DB_New_T <> DB_Old_T and trim(oRS.fields("Type")) = "T" then blnWriteUseDB = 1 DB_Old_T = DB_New_T end if if DB_New_V <> DB_Old_V and trim(oRS.fields("Type")) = "V" then blnWriteUseDB = 1 DB_Old_V = DB_New_V end if select case trim(ucase(oRS.fields("Type").value )) case "T" call TableScript( oRS.fields("Server"), oRS.fields("DB"), oRS.fields("object"), oRS.fields("Note"), _ "c:\temp\migration_tbl.sql", "c:\temp\migration_ind.sql", "jeffrey.yao", "jy", blnWriteUseDB) case "V" call ViewScript( oRS.fields("Server"), oRS.fields("DB"), oRS.fields("object"), oRS.fields("Note"), _ "c:\temp\migration_vw.sql" , "jeffrey.yao", "jeffreyy", blnWriteUseDB) case else msgbox ("Invalid object type is encountered")

end select blnWriteUseDB = 0 oRS.MoveNext loop end if set oRS = nothing set oCnn = nothing '----------------------------- ------------------------------ ----------------- 'Function: Based on the input parameter, the proc will write two files '----------------------------- ------------------------------ ----------------- Sub TableScript (byval Server, byval DB, byVal Object, byVal Note, byVal TableFileUNC, byVal IndexFileUNC, byVal uid, byval pwd, byval WriteUseDB) dim oServer, oDB, oTbl dim fso, fl, fl_idx if isnull(Note) then Note = "" if trim(cstr(Note)) <> "" or cint(WriteUseDB) = 1 then set fso = CreateObject("Scripting.FileSystemObject") set fl = fso.OpenTextFile( cstr(TableFileUNC), ForAppending, true ) set fl_idx = fso.OpenTextFile( cstr(IndexFileUNC), ForAppending, true ) if trim(cstr(Note)) <> "" then fl.writeline ( "--" & cstr(Note) ) end if if cint(WriteUseDB) = 1 then fl.writeline ( "use " & cstr(DB) & vbCrLf & "go" ) fl_idx.writeline ( "use " & cstr(DB) & vbCrLf & "go" ) end if fl.close set fl = nothing set fl_idx = nothing set fso = nothing end if set oServer = CreateObject("SQLDMO.SQLServer") OServer.connect cstr(Server), cstr(uid), cstr(pwd) set oDB = oServer.databases(cstr(DB) ) set oTbl = oDB.tables(cstr(object) ) oTbl.script SQLDMOScript_IncludeHeaders + SQLDMOScript_ToFileOnly + SQLDMOScript_Drops + SQLDMOScript_AppendToFile + SQLDMOScript_Default + SQLDMOScript_DRI_PrimaryKey + SQLDMOScript_OwnerQualify, cstr(TableFileUNC) oTbl.script SQLDMOScript_IncludeHeaders + SQLDMOScript_ToFileOnly + SQLDMOScript_AppendToFile + SQLDMOScript_DRI_ForeignKeys + SQLDMOScript_OwnerQualify, cstr(IndexFileUNC) set oTbl = nothing set oDB = nothing set oServer = nothing end sub '----------------------------- ------------------------------ ----------------- 'Function: Based on the input parameter, the proc will write to one file '----------------------------- ------------------------------ ----------------- Sub ViewScript (byval Server, byval DB, byVal Object, byVal Note, byVal ViewFileUNC, byVal uid, byval pwd, byval WriteUseDB) dim oServer, oDB, oView dim fso, fl if isnull(Note) then Note = "" if trim(cstr(Note)) <> "" or cint(WriteUseDB) = 1 then set fso = CreateObject("Scripting.FileSystemObject") set fl = fso.OpenTextFile( cstr(ViewFileUNC), ForAppending, true ) if trim(cstr(Note)) <> "" then fl.writeline ( "--" & cstr(Note) ) if cint(WriteUseDB) = 1 then fl.writeline ( "use " & cstr(DB) & vbCrLf & "go" ) fl.close set fl = nothing set fso = nothing end if set oServer = CreateObject("SQLDMO.SQLServer") OServer.connect cstr(Server), cstr(uid), cstr(pwd) set oDB = oServer.databases(cstr(DB) ) set oView = oDB.views(cstr(object) ) oView.script SQLDMOScript_IncludeHeaders + SQLDMOScript_ToFileOnly + SQLDMOScript_Drops + SQLDMOScript_AppendToFile + SQLDMOScript_Default + SQLDMOScript_OwnerQualify, cstr(ViewFileUNC) set oView = nothing set oDB = nothing set oServer = nothing end sub

After running command "cscript MigrateObject.vbs" in command line (in Window 2000/XP prof) , we will get three SQL script files in c:\temp folder, they are migratation_tbl.sql, migration_ind.sql and migration_vw.sql.

3. (optional) Compose two batch files for real migration purpose, one is to clean the migrated objects and another is to migrate objects.

One note here is that when we run the clean.bat, usually we will get errors indicating that one table cannot be dropped because it is referenced by another table, do not worry, just run it again and again until no such errors occur because each time we run it, it will drop the "leaf level" tables that are not being referenced, and if we continue to run it, it will eventually delete the "root level" tables. (Yes, I can write a script that drop all tables by running only once, but I am not that kind of perfectionist as I'd rather explore other interesting fields than spend the time to make it a perfection. Remember that 80 / 20 rule? )

Migrate.bat is illustrated in the following,

@echo off
time /T
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\migration_tbl.sql
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\migration_ind.sql -o c:\temp\ErrIdx.txt
osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\migration_vw.sql
time /T

Clean.bat is illustrated in the following

@echo off osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\drop_tbl.sql osql -S UAT -U jeffrey.yao -P jy -n -i c:\temp\drop_vw.sql

Note: drop_tbl.sql can be extracted from migration_tbl.sql by only copying the header part of each object generation script, it includes these two lines for each table:
If exists (select * from dbo.sysobjects where id = object_id(N'Author') and objectproperty(id, N'IsUserTable') = 1)
drop table Author
Same for drop_vw.sql. I actually use VBScript to generate the drop_tbl.sql and drop_vw.sql, all I need to do is use SQLDMOScript_Drops only in the VBScript to generate these scripts.

Usage In Real World

Step 1. I will put the Excel sheet file in a shared network drive and ask people who are responsible for all new database objects to fill in the sheet what need to be migrated. (Is not this a great team work? It is less likely one object will be missed than if I were responsible to fill in the sheet)

Step 2. I will run the "Migration Prep Engine" (MigrateObject.vbs) to generate the migration scripts.

Step 3. Run the two batch files against my own environment to test the script.

Step 4. Run the batch files to implement the migration to a target server.

Step 5. Save the scrip and the MigrationObjet.xls Excel sheet file into VSS and label it for future reference.

By adopting this new method, it seems it won't take me an hour to migrate tens of hundreds of objects, and here I really enjoy the benefits of the two "DBA laws" mentioned above.


In this article, I introduced a new approach to migrate database objects with self-defined migration rules (protocol) in a Excel sheet file, which can be shared by all team members. This approach aims to leverage the team work and make the migration objects well documented. It is flexible and may be easily customized to suit various migration scenarios so long as you modify the "Migration Prep Engine" to adapt to your new migration protocol in the Excel sheet file.

Total article views: 10201 | Views in the last 30 days: 3
Related Articles

Generating Scripts for Database Objects

Creating Seperate Scripts Per Object


How to Get the Scripts for SQL Server Objects

Using VB Script allows one to script out all the Sql Server objects in independent file.






during migration


Scripting Objects Including Permissions

Scripting Objects Including Permissions