SQL Server Migration

A recent task at work was supposed to be easy but ended up being much more of a headache than I expected. The task: migrate a SQL Server 2000 database to SQL Server 2005 while keeping the 2000 database live. Ahhh, Microsoft… I should have known it wouldn’t be easy.

Other restrictions that made this a challenge:

• I only have SQL Server 2005 Express on my local computer.
• I have limited access to the SQL Server 2005 database server that I need to migrate to.

The requirement to keep the 2000 db live meant that I could not detach it.

My first thought was to follow these directions from Microsoft: http://msdn.microsoft.com/en-us/library/ms190436.aspx.
I didn’t follow the directions exactly: I did the backup on the 2000 server and then asked someone who had admin privileges to copy the backup file to the other server (remember, I don’t have access). But, when I tried to restore, I got an error:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'mydbname' database. (Microsoft.SqlServer.Express.Smo).

My second try was to follow these directions, also from Microsoft: http://msdn.microsoft.com/en-us/library/ms188664.aspx.
However, I don’t have the copy wizard on my 2005 Express, so no fancy wizards for me…

Last and final attempt came from this very useful article in Pinal Dave’s blog and although the error he describes is different than mine, it was the perfect solution. In SQL Server 2005, use the following:

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH 
ROLLBACK IMMEDIATE

RESTORE DATABASE AdventureWorks
    FROM DISK = ‘C:BackupAdventureworks.bak’
            WITH MOVE ‘AdventureWorks_Data’ TO ‘C:\Data\datafile.mdf’,
            MOVE ‘AdventureWorks_Log’ TO ‘C:\Data\logfile.ldf’,
            REPLACE

Use your own database name, data filenames, and paths of course. If you don’t know the names to add to the MOVE command, follow Kevin Jansz’s suggestion from the same blog (see comment #7) to use the following command:

RESTORE FILELISTONLY FROM DISK=’C:\BackupAdventureworks.bak’


Why this blog.

One never knows how to begin a first post, so I won’t have much to say here, except to say that this blog is my attempt to stand on the shoulders of giants in the computing and blogging world.

Suffice to say that any time I need to find some information online, the most useful is generally in other peoples’ blogs, answers to questions, and comments. But it’s scattered, and I often find that I spend a lot of time putting it all together.

This blog is my attempt to do that, and hopefully help some others in the process.