Download the script here: sp_loaddata.sql

Overview: This script will take an entire server and migrate the data to another server.  It walks down database by database, table by table.  It does require the table schemas to match on the two servers.  (A future enhancement will detect any schema differences, so check.)  There are four variables at the beginning where you specify the source and destination servers and passwords and has been designed to operate under the sa account.  This script does the following:  turn on select into/bulk copy on the destination server, turn on truncate on checkpoint on the destination servver (to prevent the tran log from filling up with the extent allocations), truncate the destination table, drop the indexes on the destination table (for speed), bcp out the data from the source server, bcp the data into the destination server, recreate the indexes, reset the dboptions for select into bcp and truncate on checkpoint to what they are on the source server.  This script will clean up after itself and leave no trace of the bcp files when done.  The only changes you might need to make to this are the directory locations for the BCP files.  Future enhancements are to turn off declarative RI, compare the table schemas on the source and destination servers before truncating, and add two optional parameters such that you can do a single database or a single table.   (These enhancements should be added in sometime within the next couple of weeks.)

On the face of it, this seems to be a rather simply a straightforward script.   But, there were a few intracacies that came about during the development of this script that will be explained below.

Right at the beginning, you will not that this script is created with encryption.   What the encryption option does is to encrypt the contents of syscomments for this procedure so that they can not be viewed since you need to add passwords to the script.   The script starts off as normal with a bunch of variable declarations and initialization.  The thing to note at the end of the initialization section is that you need to add in the source and destination servers as well as the sa password for the source and destination servers.

After the initialization stage, three temporary tables are created.  (I'm about as anti-temp table as you can get, so these two are absolutely necessary.)  The first table will contain a list of tables that we are going to operate on.  The reason this was needed is because you can not change database context within a stored procedure.   I needed to get the list of tables to process from sysobjects.  This could easily be accomplished via a dynamic select statement as is noted below, but, this could not be done inside of a cursor declaration.  The select statement for the cursor declaration would have needed to be dynamic in this case, because I was processing multiple databases.  But when you issue an exec(statement), the statement is essentially run in its own memory space.  It can not use any variables outside of the batch, nor is the cursor available after the exec.  To get around this limitation, the decision was made simply to dump the list of tables into a temp table and declare the cursor against the temp table for processing.  The table that holds the list of indexes to drop was created for the same reason.  The third table was created due to the limitation of 255 characters in a variable.  I needed to be able to construct a create index statement that would replace the index that was dropped.  This statement could be longer than 255 characters and as such could not be put into a variable to process within an exec statement.  The purpose of this table will be further explained below.

Naming Conventions 1 2 3