Recently I needed to transfer about 50 smallish databases from one machine running SQL Server 2005 to another machine running SQL Server 2008 R2. These were all production databases that had the potential to be in use 24/7 so the transfer needed to take as little time as possible. I built several scripts that in turn built other scripts that helped me accomplish this task. I ended up breaking the process down into four scripts. The first script detached all of the databases. The second script was actually a batch file that copied the files from one machine to the other. In my case, on the original server the mdfs and ldfs were all on the same array. The copy script actually split them to put the mdfs on one array and the ldfs on another. The third script attached the databases on the new machine. The fourth one simply changed the compatibility level of the databases to 2008 R2.
First, I had to build the commands for each of the databases. I used a script to build everything and copied the result to a file for each of the steps. There were a few items to keep in mind. Before using any of the scripts to detach databases, the databases must not be in use. You can either verify this ahead of time or put the server into single-user mode. Something I ran into with the detach script is that I needed to run it as ‘sa’ or else the Windows permissions got jacked and copying the files in later steps didn’t work. I’m sure it’s something simple but I didn’t research it at the time. I bet someone has the answer why. But back to the scripts.
Create a script to detach the databases from the current server.
SELECT 'USE [master] GO' SELECT 'EXEC master.dbo.sp_detach_db @dbname = N''' + D.name + ''' GO ' FROM sys.databases D WHERE D.database_id > 4 ORDER BY D.name
The script simply uses the sys.databases table and constructs an EXEC statement to call sp_detach_db for each user database on the server. Notice that I limit the results to database_id greater than 4. Typically the first four databases are system databases. You can tweak the number if this doesn’t hold true on your server. Run the script to generate all of the detach statements. You can run the results to Text and do a copy/paste into another file. You can also run the results directly to a file.
Next, create a script to copy the files from the old server to the new server.
SELECT 'robocopy \\OLDSRV\D$\MSSQL\MSSQL.1\MSSQL\Data \\NEWSRV\E$\ ' + RTRIM(substring(right(DbFile.physical_name,charindex('\',reverse(DbFile.physical_name))),2,100)) FROM master.sys.databases D JOIN master.sys.master_files DbFile ON DbFile.database_id = D.database_id WHERE D.database_id > 4 ORDER BY D.name
This script creates a line for each file to use robocopy for transfering the file from the old server to the new server. I copied this into a batch file. Another option would be to use this within a Powershell script. With this I copy the databases from one directory on the old server to a different directory on the new server. This was my need but there are all kinds of ways to use this. I happened to also have all of the databases in the same directory. This may not be true in all cases and the script may need to make use of the full path in physical_name of sys.databases.
The third script creates the SQL to attach the databases on the new server.
SELECT 'CREATE DATABASE [' + D.name + '] ON ( FILENAME = N''E:\' + RTRIM(substring(right(DbFile.physical_name,charindex('\',reverse(DbFile.physical_name))),2,100)) + '''),(FILENAME = N''E:\' + RTRIM(substring(right(LogFile.physical_name,charindex('\',reverse(LogFile.physical_name))),2,100)) + ''' ) FOR ATTACH GO ' FROM sys.databases D JOIN sys.master_files DbFile ON D.database_id = DbFile.database_id AND DbFile.type = 0 JOIN sys.master_files LogFile ON D.database_id = LogFile.database_id AND LogFile.type = 1 WHERE D.database_id > 4 ORDER BY D.name SELECT 'CREATE DATABASE [' + D.name + '] ON ( FILENAME = N''' + DbFile.physical_name + ''' ),( FILENAME = N''' + LogFile.physical_name + ''' ) FOR ATTACH GO ' FROM sys.databases D JOIN sys.master_files DbFile ON D.database_id = DbFile.database_id AND DbFile.type = 0 JOIN sys.master_files LogFile ON D.database_id = LogFile.database_id AND LogFile.type = 1 WHERE D.database_id > 4 ORDER BY D.name
There are two creation scripts here. The first one creates the script to attach the databases for the new server. It attaches the files from their new locations used during the copy step. The second script creates the script to attach the databases back on the old server from their original locations. This is in case you still want the old server or if something went wrong during the transfer process.
The last script is used to update the compatibility mode of the newly attached databases on the SQL Server 2008 R2 server.
SELECT 'ALTER DATABASE [' + D.name + '] SET COMPATIBILITY_LEVEL = 100 GO' FROM sys.databases D WHERE D.database_id > 4 ORDER BY D.name
When you restore a 2005 database to a 2008 R2 server the database gets upgraded automatically. However, the database is set to compatibility mode 90, which is SQL Server 2005. This script will update the compatibility mode to 100, which is SQL Server 2008 R2.
After following these steps you can have your SQL Server 2005 databases moved to a SQL Server 2008 R2 machine in no time.
I want to give a reference to Aaron Nelson (@sqlvariant). He had a blog post that got me started down this path.