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

MORE INFORMATION

• 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.

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