You cannot restore system database backups to a different build of SQL Server

The restore process in such a situation is fairly involved, and used only as a last resort.

The first step in trying to work around this problem is to determine the current build of the server and the SQL Server build on which the backup was performed.

Microsoft SQL Server 2000 and Microsoft SQL Server 2005 provide the following two methods to determine the current SQL Server build:

• Method 1

  SELECT @@version

  This code returns text similar to:
  
  

  
  Microsoft SQL Server  2000 - 8.00.192 (Intel X86)
  Jul 31 2000 15:47:46
  Copyright (c) 1988-2000 Microsoft Corporation 
  Enterprise Edition on Windows NT 5.0 (Build 2195: )
  (1 row(s) affected) 

• Method 2

  SELECT SERVERPROPERTY('ProductVersion')

  This code returns text similar to:
  
  

  
  8.00.192
  (1 row(s) affected) 

For the purpose of this article, this build number is referenced as the "SQL Server Build" in the remainder of this article.

You can determine the build on which the backup occurred by using the following command on the backup file:

RESTORE HEADERONLY
FROM DISK = 'd:\temp\master176.bak' 

In the output that is generated, the following three (3) columns provide the server build information:

SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild
-------------------- -------------------- --------------------
8                    0                    176

The actual output has several other columns; however, only the columns that are of interest are included in this article for clarity. 

For the purpose of this article, this build number is referenced as the "Backup Build" for the remainder of this article.

In the example, the "SQL Server Build" is 8.00.192, while the "Backup Build" is 8.00.176. Because the two builds are different, the restore operation is not allowed and error message 3168 occurs.

The restore process to work around this problem varies depending on which system database the user is trying to restore. As a precautionary measure, Microsoft recommends that you make a backup of all your existing databases (system and user databases) before you proceed with any of these steps.

If the restore attempt is on the master database, you can use the following steps to attempt a successful restore:

1. Detach all user databases that exist on the system by using the sp_detach_db stored procedure. For more information about detaching user databases, please refer to the "Attaching and Detaching Databases" topic in SQL Server 2000 Books Online. If you are using SQL Server 2005, see the "Detaching and attaching a database" topic in SQL Server 2005 Books Online. As a cross check, the following query returns a list of all user databases on the system:
   
   

   SELECT name FROM SYSDATABASES
   WHERE dbid > 4

2. Backup the msdb and model databases on the system by using the following commands:

   BACKUP DATABASE MSDB TO DISK = '<backup device>' WITH NOINIT, NOSKIP
   BACKUP DATABASE MODEL TO DISK = '<backup device>' WITH NOINIT, NOSKIP

3. If the "Backup Build" is larger than the "SQL Server Build," install all the necessary service packs or hotfix builds to roll the server software forward to the "Backup Build" that was determined previously. Then, go to step 6.
4. Remove SQL Server 2000 or SQL Server 2005. For more information about removing SQL Server 2000, please refer to the "How to remove SQL Server 2000" topic in SQL Server 2000 Books Online.
5. Reinstall SQL Server 2000 or SQL Server 2005 and all the necessary service packs or hotfix builds to roll the server software forward to the "Backup Build" that was determined previously.
6. Start the server in single-user mode and restore the master database by using the backup file for this build. For instructions about restoring the master database, please refer to the "Restoring the Master database from a Current Backup" topic in SQL Server 2000 Books Online. If you are using SQL Server 2005, see the "Restoring the master database" topic in SQL Server 2005 Books Online.
7. After you successfully restore the master database, you must shut down and restart the SQL Server service.
8. If the "Backup Build" is larger than the "SQL Server Build", go to step 10.
9. Reapply any service packs or hotfix builds needed to roll the server software forward to the "SQL Server build".
10. Restore the msdb and model databases from the backups created in step 2. If you are using SQL Server 2005, see the "Restoring the model and msdb databases" topic in SQL Server 2005 Books Online.
11. Reattach all user databases by using the sp_attach_db stored procedure. For more information on how to reattach the database, please refer to the "Attaching and Detaching Databases" topic in SQL Server 2000 Books Online.

Any changes that were made to the master database after the backup of the master was performed are now lost and must be manually reapplied.

If you attempt the restore operation on the model or the msdb database, you can use the following steps to work around this problem. To illustrate this procedure with an example, this article refers to the original server on which one of these databases, say msdb, is to be restored as Server A. This procedure involves installing SQL Server on a different Server B to avoid the 3168 error message. The assumption of course, is that you only have a valid backup from an older build for the msdb on Server A.

1. Install SQL Server 2000 on Server B (with the same collation settings as Server A) and any service packs or hotfix builds necessary to get the server software to the "Backup Build".
2. After SQL Server 2000 has been installed, start the SQL Server service and try to restore the msdb database from the backup file. The restore works if the server build and the build on which the backup was performed are identical.
3. If the "Backup Build" is larger than the "Current Build", go to step 5.
4. After a successful restore, upgrade SQL Server 2000 on Server B by applying any service packs or hotfix builds necessary to roll the server software forward to the "Current Build".
5. After the upgrade completes, perform a backup of the msdb database. Now you are able to restore from this backup on Server A, considering that the build of the server on which the backup was performed and the "Current Build" are identical.

The same procedure works for the model database as well. Again, any changes that are made to these databases since the backup was performed are lost and must be manually reapplied.