Moving SQL Server 7.0 databases to a new server with BACKUP and RESTORE

This article describes how to restore database backups for users and systems onto another SQL Server 7.0 installation that has different drive letter configurations from the server to which you originally backed up the databases. You can use this method when you want to move a database by using database backups or database restores. For more information about using an alternative method, click the following article number to view the article in the Microsoft Knowledge Base: NOTE: These instructions in this article do not apply to SQL Server 2000.

You cannot restore the SQL Server 7.0 master, model, msdb or distribution system databases to SQL Server 2000. SQL Server 7.0 system databases are not compatible with SQL Server 2000.

The examples in this article are based on the following assumptions:

• The destination installation of SQL Server 7.0 is in the D:\Mssql7 directory, and the database files and log files are located in the default D:\Mssql7\data directory.
• The source installation of SQL Server 7.0 is in the C:\Mssql7 directory, and the database file and log files are located in the default directory C:\Mssql7\data.

Prerequisites

• Make a current backup of all databases from their current location, particularly for the master database, the model database, and the msdb database.
• You need to have system administrator privileges.
• You need to know both the logical name and the physical names of all data and log files for the databases that you want to restore.
  
  NOTE: To determine the logical name and the physical name of all the files that you want to restore from the backup set, run the following command in a Query Analyzer connection where mydbdata.bak is the name of the file that contains the database backup:

  RESTORE FILELISTONLY FROM DISK='d:\mssql7\backup\mydbdata.bak'
  go
  					

• The source and the destination SQL Server 7.0 installations should have the same service packs and patches applied.

If you encounter problems during the process and you cannot access a database that you moved, or if you cannot start SQL Server, check the SQL Server error log and SQL Server 7.0 Books Online for details on the errors that you encounter.

To restore the databases

1. Restore the master database.
   
   In the following example, the master database contains one data file, Master.mdf, and one log file, Mastlog.ldf, from the full database backup file, Master_db.bak.
   1. Run the following command from a command prompt to start SQL Server:

      d:\mssql7\binn\sqlservr -c -m

      
      NOTE: The -m switch starts SQL Server in single-user mode. In single-user mode, you cannot successfully make more than a single connection. Please be aware of any other clients or services that could make that single connection before you connect with SQL Server Query Analyzer.
   2. In SQL Server Query Analyzer, use the following syntax to restore a backup of the master database:

      RESTORE DATABASE master FROM disk='d:\mssql7\backup\master_db.bak'
      WITH MOVE 'master' to 'd:\mssql7\data\master.mdf',
      MOVE 'mastlog' to 'd:\mssql7\data\mastlog.ldf',
      REPLACE
      go

      NOTE: SQL Server shuts down automatically after you restore the master database.
   3. Run the following command from a command prompt to start SQL Server:

      d:\mssql7\binn\sqlservr -c -f -T3608 -T4022

      NOTE: By using this syntax, you can perform system table updates.
   4. In SQL Server Query Analyzer, execute the following set of statements:
      
      WARNING: Updating system tables incorrectly can cause serious problems that may require you to reinstall your SQL Server installation. Microsoft cannot guarantee that problems resulting from the incorrect updating of system tables can be solved. Update system tables at your own risk.

      USE master
      go
      UPDATE sysdatabases SET filename='d:\mssql7\data\tempdb.mdf' WHERE name='tempdb'
      go
      ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'd:\mssql7\data\tempdb.mdf')
      ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'd:\mssql7\data\templog.ldf')
      go

      You should receive the following messages confirming the change:
      File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
      
      File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
2. Restore the model database.
   
   In the following example, the model database contains one data file Model.mdf, and one log file, Modellog.ldf, from the full database backup file, Model_db.bak. If you do not have a backup of the model database or if you want to use the one that is installed by default during the SQL Server installation, you can remap the model database. This procedure is similar to the procedure that is described for the northwind database and the pubs database in step 4.
   1. In SQL Server Query Analyzer, execute the following stored procedure to detach the current model database from SQL Server:

      exec sp_detach_db N'model'
      go

      After you execute the stored procedure, you receive the following message:
      Successfully detached database 'model'
      DBCC execution completed. If DBCC printed error messages, contact your system administrator
      NOTE: You need to detach the model database because you cannot directly overwrite it by using the RESTORE statement. You have to remove the system table references for this system database before the database is restored. In addition, you cannot drop the database for the purpose of removing those system entries.
   2. In SQL Server Query Analyzer, use the following syntax to restore a backup of the model database:

      RESTORE DATABASE model FROM disk='d:\mssql7\backup\model_db.bak'
      WITH MOVE 'modeldev' TO 'd:\mssql7\data\model.mdf',
           MOVE 'modellog' TO 'd:\mssql7\data\modellog.ldf',
           REPLACE
      go

      Note The connection to SQL Server is broken after you restore the model database.
3. Restore the msdb database.
   
   In the following example, the msdb database contains one data file, Msdbdata.mdf, and one log file, Msdblog.ldf, from the full database backup file, Msdb_db.bak.
   1. Run the following command from a command prompt to start SQL Server:

      d:\mssql7\binn\sqlservr -c -f -T3608 -T4022

   2. In SQL Server Query Analyzer, execute the following stored procedure to detach the msdb database:

      exec sp_detach_db N'msdb'
      go

   3. In SQL Server Query Analyzer, use the following syntax to restore a backup of the msdb database:

      RESTORE DATABASE msdb FROM disk='d:\mssql7\backup\msdb_db.bak'
      WITH MOVE 'msdbdata' TO 'd:\mssql7\data\msdbdata.mdf',
           MOVE 'msdblog' TO 'd:\mssql7\data\msdblog.ldf',
           REPLACE
      go

      Note The connection to SQL Server is broken after you restore the msdb database.
4. The northwind and pubs databases:
   
   After you restore the master database, the system table entries for the northwind database and the pubs database are changed and the two database, like all of the other user databases, are in Suspect mode. If you want to reuse the original database files and re-attach them to the server, you can use the following example. Otherwise, if you want to restore a copy from backup, use the mydb database example that is described in step 6. Use the following example to update the system table references for the northwind database. The northwind database contains one data file, Northwind.mdf, and one log file, Northwind.ldf.
   1. Run the following command from a command prompt to start SQL Server:

      d:\mssql7\binn\sqlservr -c -f -T3608 -T4022

   2. In SQL Server Query Analyzer, execute the following stored procedure to detach the northwind database:

      exec sp_detach_db N'northwind'
      go

      
      NOTE: You may receive the following message that indicates that there is no access to physical files:
      Server: Msg 947, Level 16, State 1, Line 1 Error while closing database 'Northwind' cleanly.
      Successfully detached database 'northwind'.
      DBCC execution completed. If DBCC printed error messages, contact your system administrator.
   3. In SQL Server Query Analyzer, use the following syntax to attach the northwind database:

      exec sp_attach_db N'northwind', N'd:\mssql7\data\northwnd.mdf', N'd:\mssql7\data\northwnd.ldf'
      go

5. Remap the pubs database.
   
   The pubs database contains one data file, Pubs.mdf, and one log file, Pubs_log.ldf. You can remap the pubs database in the same way that you remapped the northwind database.
6. Restore the mydb database.
   
   The mydb database contains one data file, Mydbdata.mdf, and one log file, Mydblog.ldf, from the full database backup file, Mydb_db.bak.
   1. In SQL Server Query Analyzer, execute the following stored procedure to detach the mydb database:

      exec sp_detach_db N'mydb'
      go

   2. In SQL Server Query Analyzer, use the following syntax to restore a backup of the mydb database:

      RESTORE DATABASE mydb FROM disk='d:\mssql7\backup\mydb_db.bak'
      WITH MOVE 'mydbdata' TO 'd:\mssql7\data\mydbdata.mdf',
           MOVE 'mydblog' TO 'd:\mssql7\data\mydblog.ldf',
           REPLACE
      go

7. Rename SQL Server.
   
   IMPORTANT: This procedure is required only if you have to change the name of the SQL Server installation to which you have just restored the master database.
   
   For example, you would rename SQL Server if:
   
   
   • You restored the database backups from a computer that is named SOURCESVR to a computer that is named TARGETSVR.
   • The computer that is named TARGETSVR has a SQL server that is named SOURCESVR.
   If you need to rename SQL Server, use the follow syntax to synchronize the computer name and the SQL Server name:

   exec sp_dropserver 'SOURCESVR'
   go
   exec sp_addserver 'TARGETSVR', 'LOCAL'
   go

8. Restart the SQL Server service.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base: NOTE: For additional information about other startup options such as -c, -m, and -f, please refer to SQL Server Books Online.