Restore SQL Server 2005 Backup to SQL Server 2008 R2 WITH STANDBY

Question

Restore SQL Server 2005 Backup to SQL Server 2008 R2 WITH STANDBY

Welsh Corgi

SSC Guru

Points: 116520
More actions
August 18, 2012 at 12:09 pm

#265478

I tried to RESTORE a SQL Server 2005 Backup to a 2008 R2 Server and I get the following error:
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.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I have 24 Databases that I need to move. Some of which are quite large.
I wanted to be able to perform a Complete back restore the Database and later apply a differenential Backup.
Any ideas on how I can make this happen?
Edit, I meant to post to DBA Admin Forum.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Do the restore WITH NORECOVERY. The only options allowed when upgrading are WITH RECOVERY or WITH NORECOVERY.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 2

I removed because my suggestion was incorrect, I wasn't paying close attention - apologies Welsh Corgi... thanks for pointing out Gilamonster. 🙂

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

GilaMonster (8/18/2012)
Do the restore WITH NORECOVERY. The only options allowed when upgrading are WITH RECOVERY or WITH NORECOVERY.

+1

Whether using script or the GUI, use norecovery.

You can bring the databases online then with "RESTORE DATABASE xX WITH RECOVERY"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sqlsurfing (8/18/2012)
If you take your SQL 2005 database mdfs and ldf... and sp_attach or use gui (prob easier with so 20+ DBs to use script) to attach the files to your SQL 2008 server, then attempt restore it should work.

Err... what? Why attach the DB and then restore over the top of the attached DB?

A restore replaces the existing files with what's in the backup, so doing that will upgrade the DB on attach, then replace the upgraded DB with the contents of the backup, which will be upgraded again (and still won't allow standby to be used because of the upgrade)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

GilaMonster (8/18/2012)
Do the restore WITH NORECOVERY. The only options allowed when upgrading are WITH RECOVERY or WITH NORECOVERY.

Thank you Gail!:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Steve Jones - SSC Editor (8/18/2012)
GilaMonster (8/18/2012)
Do the restore WITH NORECOVERY. The only options allowed when upgrading are WITH RECOVERY or WITH NORECOVERY.
+1
Whether using script or the GUI, use norecovery.
You can bring the databases online then with "RESTORE DATABASE xX WITH RECOVERY"

I just had to edit the scripts. The Databases were supposed to be moved and stay at 2005 but that got put on hold. Good thing because I have SQL Server 2008 R2 x64 bit which is a lot better than I would have had.

I had saved all of my scripts and all that I had to do was sit them with the NORECOERY option.

I have completed the restore on 6 Database so far with 3 in progress.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/