How to migrate a Microsoft SQL 7 or 2000 server instance to another server

How to migrate a Microsoft SQL 7 server instance to another server

I needed to replace the hardware that a SQL server resided on and move the SQL instance to a new server.

I started with this excellent guide as a reference point

After some trial and error this is the procedure I followed.

On the existing server

  • Ensure that no users or applications have access to the databases in the SQL instance
  • Backup all the SQL databases to a folder. I used Query Analyser and a script like the one below which backs up all the databases with the exception of ‘tempdb’ to the location C:\SQLBkup

DECLARE @name VARCHAR(50), — database name

@path VARCHAR(256), — path for backup files

@fileName VARCHAR(256), — filename for backup

@fileDate VARCHAR(20) — used for file name

CREATE TABLE [dbo].#tempBackup (name VARCHAR(200), flag BIT)

SET @path = ‘C:\SqlBkup\’

— Includes the date in the filename

SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)

— Includes the date and time in the filename

–SET @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + ‘_’ + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ‘:’, ”)

INSERT INTO [dbo].#tempBackup (name, flag)

SELECT name, 0

FROM master.dbo.sysdatabases

WHERE name NOT IN (‘tempdb’)

WHILE EXISTS(SELECT TOP 1 name FROM [dbo].#tempBackup WHERE flag = 0)



@name = name,

@fileName = @path + name + ‘_’ + @fileDate + ‘.BAK’ — Unique FileName

–@fileName = @path + @name + ‘.BAK’ — Non-Unique Filename

FROM [dbo].#tempBackup

WHERE flag = 0

— does not overwrite the existing file

— BACKUP DATABASE @name TO DISK = @fileName

— overwrites the existing file (Note: remove @fileDate from the fileName so they are no longer unique


UPDATE #tempBackup

SET flag = 1

WHERE name = @name

AND flag = 0


DROP TABLE [dbo].#tempBackup

  • Copy the backup directory off the server to a location that will be available to your new server
  • Power off your existing server

On the new server

  • Configure the new server to have the same drives and locations as the old one. If the databases were stored on D: and Transaction Logs on K: then ensure that the drives are exactly the same. The restore process will be looking for these when the database and transaction logs are restored
  • Ensure that the server name, domain membership and IP address are configured to be exactly the same as the old server
  • Install the same version of MS SQL matching the program file and data directory locations on the old server.  Patch as necessary
  • Stop the MSSQL Server service and instead open the SQL server in single user mode. To do this open a command prompt, use the cd command to locate the mssql\sqldata\binn directory or the folder where sqlservr.exe is located and type sqlservr –m
  • Open Query Analyser and restore the MASTER database with the following syntax replacing the location and name of the backup file to match your location

RESTORE DATABASE [Master] FROM DISK = ‘c:\sqlbkup\Master_121208.BAK’

Once the MASTER database is restored MSSQLServer service automatically shuts down.

  • Restart the MSSQLServer service and reopen Query Analyser. Restore MSDB and Model databases again with the following syntax replacing the location and name of the backup file to match your location

RESTORE DATABASE [Model] FROM DISK = ‘c:\sqlbkup\Model_121208.BAK’


  • Once the Model and MSDB databases have been restored check Enterprise Manager to see whether user accounts and local packages have been restored
  • Now restore the user databases using Query Analyser and the syntax used for restoring the system databases. Simply replace the database name and backup location

I experienced some problems at this point as I found that my user databases had been partially created when Model and MSDB databases were restored. All the user databases were marked as ‘Suspect’ and I was unable to restore the user databases from backup because SQL server saw that the suspect databases existed but were inaccessible. To resolve this I had to delete each suspect user database in Enterprise Manager but received an error message after each deletion that the database could not be deleted. However, on restarting EM the databases had indeed been deleted and I could safely restore the user databases from backup

That should be it and the next point would be to have some key users test the system for a while before any data entry starts. Check all your settings and that all Database Maintenance plans and SQL jobs run as scheduled


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s