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 10th 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 http://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:
Read the rest of this entry . . .
