The next table is then fetched from the inner cursor (the one at level 2 in the nesting) and the whole process is repeated.  Once all tables have been processed, the database options are reset according to the settings on the source database.  Two things to note from the sysdatabases table:  status & 8 = truncate on checkpoint and status & 4 = select into/bulkcopy.

The next database in the outer cursor is then fetched and the process continues.   Once all databases have been processed, the final cleanup is done where all cursors are deallocated and all tables dropped.

You'll note the extensive use of comments, print statements, and selects to return information from the procedure.  This allows for tracking the progress to a very fine level.  At any given point, the procedure will tell you which database, table, and process that is being preformed.  The other thing to note about the design is the minimal impact.  This was designed to be capable of running while the destination and source databases were being accessed if needed.  The procedure only works with a single table at a time.  So at any given time, only one table's data will be missing.   The most important thing to remember is to create this procedure with encryption as passwords are needed to make this run.  However if you are running in a secured environment, the following changes can be easily accomplished:  move the passwords to parameters for the procedure and then run this procedure from a batch file on your server that contains the passwords you need while restrciting access to the batch file.   Also, if you are running integrated or mixed security you can run isql and bcp in trusted mode and therefore be able to remove the passwords.

Stay tuned to this section as I will be adding the following enhancements in the near future:

1.  Turn off DRI before moving the data
2.  Check the table schema to make sure it matches the source table before truncating
3.  The ability to move only a single database or a single table

Naming Conventions 1 2 3