Wednesday, June 29, 2011

Msg 3180 This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

When you try to implement Log shipping from SQL Server 2008 to SQL Server 2008 R2 we cannot implement it in standby mode. The reason for this does not have anything to do with log shipping. When you try to restore the database during initialization with standby option the restore fails with the below error.

RESTORE DATABASE test FROM DISK = 'backup drive\test_full.bak' WITH STANDBY

Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Both the versions of databases are 100. Now what is the upgrade the restore is looking for?

The answer is database internal version, DBI_Version. Each page has a version number of SQL called major version in the page header. For example the version number for SQL 2008 is DBI 655 and for SQL 2008 R2 is DBI 661.

To upgrade a database you can restore the database with no recovery.

So you will not be able to implement log shipping between 2008 and 2008 R2 versions of SQL Server with standby option. Even mirroring with standby or snapshot options does not work. The reason being, one version of SQL trying to read another version of data. For the SQL to read the DBI number must be upgraded.

Happy Troubleshooting,
-Manu


1 comment: