SQL Server 2005: Automating Database Movement

Let’s say for you want to move a database from one environment (possibly production) to another (possibly development) on a semi-regular basis. After about the 10^th time doing it manually, you’re ready to automate the process. But how?

That’s what I intend to help with in this post. For this example, I’m running two Virtual servers, both running Windows Server 2003 and SQL Server 2005 SP2. We’ll use the AdventureWorks OLTP database, available for download from https://www.codeplex.com/SqlServerSamples. I’m running SQL Server and SQL Agents with local users that have permissions on both machines. This should work for SQL Server 2005 or 2008, and the Windows version level isn’t important.

There are a couple ways you could go about this task. One way would be to write stored procedures and call them with a batch file using osql.exe and SQL Agent job or your enterprise scheduler. There’s nothing wrong with that approach, but I think that for this task, SSIS packages give more options, flexibility, and simplicity for ongoing management.

At a high level, the SSIS package we are about to create will do the following:

1. back up the database locally
2. copy the database to the destination server
3. restore it over an existing database

We could skip some steps and backup the database to a server across the network, but whenever I’ve seen this in production environments, it has been problematic and can be slower than backing up locally.

Although we are not going to do this in this example, you can compress the backup with a command line backup tool like rar.exe, but be warned that some of these utilities will cause your CPU to spike and remain very high throughout the compression process. If you’re planning to use compression with very large databases, ideally you’d purchase a database backup tool from a vendor like Idera, Red-gate, or Quest, all of whom have backup software that will compress the data “in line” and have parameters for CPU affinity.

For this example I’m using the AdventureWorks sample database that I downloaded from Microsoft. If you are doing this for the first time, I strongly recommend you use two non-production servers, and also a very small database. It can even be empty.

1. Open Microsoft Visual Studio 2005 and create a new, empty Integration Services Project (SSIS package). Give it a meaningful name and click “OK.”
2. Create a directory on your destination server and share it out. Ensure you’re running SQL Server with an account that has Read, Write, and Delete permissions to it. If it doesn’t exist yet, create a directory on the master/primary server to hold the backups that will be copied to the target. It does not need to be shared out.
3. You will need to create two Maintenance Cleanup tasks. For the first one, create a connection to the primary server. Then specify the folder where the local backups are created. Choose an appropriate amount of time to store the backups—at most a few days. The extension of the file is *.bak.For the second task, specify a connection to the target SQL Server. Once you do this you will be able to enter the path where the backups are going to be copied, again choosing an appropriate number of days to retain them. The extension is the same. Close the Maintenance tasks and link them together. It doesn’t matter which one runs first.
4. Create a backup database task and edit it. Select the connection that points to the primary server, and choose the database you’d like to back up. Then specify a local path to store the backup files. If you do not have a directory set-up that will house the backups for this database only, select the option to create a subdirectory for each database backed up. Click “OK” to close the task. Link the two taks together.
5. Create an Execute SQL Task and edit it. Change the connection type to “ADO.NET” and select your local conncction. The code in step 6 needs to run against the primary server.
6. Click the three dots next to SQL Statement; a new, empty box will appear. Copy and paste the following code into the SQL Statement field. You will need to update IP/Computer names and some paths to reflect your particular setup. I’ve marked them with “[Check me]”.

   /* Snippet Begin (Start selecting text at the beginning of this line) */ 
   
   IF OBJECT_ID(N'tempdb..#FileList', N'U') IS NOT NULL 
   DROP TABLE #FileList
   
   Declare @CMD Varchar(1000), @SQLLocalBackupDir Varchar(100), @FileToCopy varchar(255), @SQLDestBackupDir varchar(100)
   Declare @CopyCommand varchar(1000)
   
   Select @SQLLocalBackupDir = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks\"' -- The local directory containing the backup file(s),  [Check me]
   Select @SQLDestBackupDir = '\\192.168.0.106\InComingBackups$\' -- The destination path for the backups. [Check me]
   
   -- List the files in the directory
   Select @CMD = 'dir ' + @SQLLocalBackupDir + ' /B /O-D'  -- Note the switches to the DIR commmand. These are important.
   Print @CMD
   
   -- Create a temp table to hold our files so that we can query it
   Create table #FileList (FileNm varchar(600) null)
   
   Insert #FileList
   	exec master..xp_cmdshell @CMD
   
   -- Get the newest filename
   Select top 1 @FileToCopy = rtrim(ltrim(FileNm)) From #FileList
   where fileNm is not null
   
   Print '@FileToCopy'
   Print @FileToCopy
   
   Select @CopyCommand = 'xcopy /Y ' + (Left(@SQLLocalBackupDir, (len(@SQLLocalBackupDir)-1))) + @FileToCopy + '"' + ' ' + @SQLDestBackupDir
   Print @CopyCommand
   
   Exec master..xp_cmdshell @CopyCommand
   
   /* Snippet End  (Stop selecting text to paste at the end of this line)*/

7. Name the task. I suggest you name the task something meaningful like “Copy back to destination server”, then save and close this step and link it to its predecessor.
8. Create another “Execute SQL” task. Change the connection type to ADO.NET and choose your destination server connection.
9. Click the three dots next to “SQL Statement” and paste in the code below. Just as in step #6, there is some editing for you to do before this will work. I’ve marked the parts with “[Check Me]”.

   /*Snippet Begin*/
   
   IF OBJECT_ID(N'tempdb..#FileList', N'U') IS NOT NULL 
   DROP TABLE #FileList
   
   Declare @CMD Varchar(1000), @SQLLocalBackupDir Varchar(100), @FileToRestore varchar(255), @SQLDestBackupDir varchar(100)
   Declare @CopyCommand varchar(1000)
   Declare @RestoreCMD varchar(1000)
   
   /*
   Setup your variables here
   */
   
   Select @SQLLocalBackupDir = 'C:\InComingBackups\' -- The local directory containing the backup file(s)  [Check me]
   
   -- List the files in the directory
   Select @CMD = 'dir ' + @SQLLocalBackupDir + ' /B /O-D'  -- Note the switches to the DIR commmand. These are important.
   Print @CMD
   
   -- Create a temp table to hold our files so that we can query it
   Create table #FileList (FileNm varchar(600) null)
   
   Insert #FileList
   	exec master..xp_cmdshell @CMD
   
   -- Get the newest filename
   Select top 1 @FileToRestore = rtrim(ltrim(FileNm)) From #FileList
   where fileNm is not null
   
   Print 'File to restore'
   Print @FileToRestore
   
   Select @RestoreCMD = 'restore database AdventureWorks from Disk = ''' + @SQLLocalBackupDir + @FileToRestore + ''''  
   
   Print @RestoreCMD
   exec (@RestoreCMD)
   
   /*Snippet End*/

10. After you’ve edited the code, save and close the Execute SQL step. Name it something appropriate such as “Restore database” and link it to its predecessor. The tasks should be executed in the order we’ve created them.
11. Save the package.
12. Before it can be tested, ensure you have a database of the same name on the destination server. It can even be empty. The only requirement is that it exists. It does not need to be stored in the same directory structure, but that drive must have sufficient space to restore the backup.

Thats it! Test the package by hitting the “Play” button or right-clicking on it in the right window pane and selecting execute. Good luck!

Troubleshooting

If things don’t work, check the following:

1. Did you update the server names and paths correctly?
2. Are you using accounts to run SQL Server that have permissions to copy files to the destination share?
3. Are you sure?
4. Are the commands/steps in your SSIS package pointing to the correct connections?
5. If the step that failed is one of the “Execute SQL” tasks, paste the code into SQL Server Management Studio (SSMS), change the output to “TEXT” and run it for a better error message. Did that help?