Microsoft KB Archive/264474

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

Article ID: 264474

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q264474



SYMPTOMS
You cannot restore a backup of a system database (master, model, or msdb) on a server build that is different from the build on which the backup was originally performed. An attempt to perform such a restore causes the following error message to occur:

Server: Msg 3168, Level 16, State 1, Line 1

The backup of the system database on device d:\temp\master.bak cannot be restored because it was created by a different version of the server (134217904) than this server (134217920).

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.



CAUSE
It is possible that the database schema for the system databases changed across server builds. In order to ensure that this change in schema does not cause any inconsistencies, one of the first checks that the RESTORE command performs is a comparison of the server build number on the backup file and the build number of the server on which the user is trying to perform the restore. If the two builds are different, the error message occurs and the restore operation terminates abnormally.

Note Installing a service pack or a hotfix build changes the server build number, and server builds are always incremental.

Here are a few scenarios in which this problem may occur:
 * A user attempts to restore a system database on Server A from a backup taken on Server B. Servers A and B are on different server builds. For example, Server A might be on a RTM build and Server B might be on a service pack 1 (SP1) build.
 * A user attempts to restore a system database from a backup taken on the same server. However, the server was running a different build when the backup occurred. That is, the server was upgraded since the backup was performed.



WORKAROUND
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 &quot;SQL Server Build&quot; 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 &quot;Backup Build&quot; for the remainder of this article.

In the example, the &quot;SQL Server Build&quot; is 8.00.192, while the &quot;Backup Build&quot; 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:   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 &quot;Attaching and Detaching Databases&quot; topic in SQL Server 2000 Books Online. If you are using SQL Server 2005, see the &quot;Detaching and attaching a database&quot; 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 </li>  Backup the msdb and model databases on the system by using the following commands: BACKUP DATABASE MSDB TO DISK = ' ' WITH NOINIT, NOSKIP BACKUP DATABASE MODEL TO DISK = ' ' WITH NOINIT, NOSKIP </li> If the &quot;Backup Build&quot; is larger than the &quot;SQL Server Build,&quot; install all the necessary service packs or hotfix builds to roll the server software forward to the &quot;Backup Build&quot; that was determined previously. Then, go to step 6.</li> Remove SQL Server 2000 or SQL Server 2005. For more information about removing SQL Server 2000, please refer to the &quot;How to remove SQL Server 2000&quot; topic in SQL Server 2000 Books Online.</li> 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 &quot;Backup Build&quot; that was determined previously.</li> 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 &quot;Restoring the Master database from a Current Backup&quot; topic in SQL Server 2000 Books Online. If you are using SQL Server 2005, see the &quot;Restoring the master database&quot; topic in SQL Server 2005 Books Online.</li> After you successfully restore the master database, you must shut down and restart the SQL Server service.</li> If the &quot;Backup Build&quot; is larger than the &quot;SQL Server Build&quot;, go to step 10.</li> Reapply any service packs or hotfix builds needed to roll the server software forward to the &quot;SQL Server build&quot;.</li> Restore the msdb and model databases from the backups created in step 2. If you are using SQL Server 2005, see the &quot;Restoring the model and msdb databases&quot; topic in SQL Server 2005 Books Online.</li> 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 &quot;Attaching and Detaching Databases&quot; topic in SQL Server 2000 Books Online.</li></ol>

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.

<div class="workaround_section">

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 &quot;Backup Build&quot;.
 * 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 &quot;Backup Build&quot; is larger than the &quot;Current Build&quot;, 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 &quot;Current Build&quot;.
 * 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 &quot;Current Build&quot; 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.

<div class="moreinformation_section">

MORE INFORMATION
It is always a good practice to have a current, effective backup strategy. This restriction is yet another example of why a good backup strategy is important. In the light of this restriction, you must perform a backup of your system databases as soon as you upgrade your server when you install either a service pack or a hotfix build.

Keywords: kbbackup kbprb KB264474

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.