November 12, 2012 at 1:23 pm
I tried to restore a SQL Server 2005 Database backup to a SQL Server 2008 R2 instance and place the Database in Standby mode using the following Code.
RESTORE DATABASE BISupport
FROM DISK = 'H:\Backups\Differential\BISupport_backup_201209101029.bak'
WITH REPLACE,
STANDBY = N'F:\MSSQL\DATA\ROLLBACK_UNDO_BISupport.BAK',
MOVE 'BISupport' TO 'F:\MSSQL\Data\BISupport.mdf',
MOVE 'BISupport_log' TO 'F:\MSSQL\Log\BISupport.ldf'
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 ran into this about 8 months ago but I can't remember what I did to get around the problem?
I want the users to be able to test and prevent them from changing the data.
Any thoughts would be greatly appreciated.
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/
November 12, 2012 at 1:25 pm
Can't be done.
In an upgrade setup, the database can only be restored with NORECOVERY or WITH RECOVERY (which ends the restore sequence)
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
November 12, 2012 at 1:29 pm
ok, thanks Gail.
After I restore with recovery what steps are required to upgrade other then setting the Compatibility Level?
Thanks again.
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/
November 12, 2012 at 1:29 pm
You have to recover the database to allow the upgrade to complete. You could however immediately set the database to read-only.
November 12, 2012 at 1:32 pm
Welsh Corgi (11/12/2012)
After I restore with recovery what steps are required to upgrade other then setting the Compatibility Level?
None are required, not even setting the compat mode is required.
You probbaly want to run a checkDB, update stats.
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
November 12, 2012 at 1:40 pm
ok, thanks again.
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/
November 15, 2012 at 3:20 pm
So does this mean that I don't need to change compatibility to 100? I restore a 2005 db on 2008 R2 and still see compatibility is 90 in sysdatabases. Maybe I'm missing something here. I was able to run sp_dbcmptlevel 'db', '100' and the change took but now just shows blank as the Compatibility Level in the UI (db options) but shows 100 in sysdatabases.
November 15, 2012 at 4:13 pm
You don't have to change it. You probably want to change it to get access to all the new language options, but you don't have to if there's a reason to stay in compat mode 90.
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
November 15, 2012 at 4:17 pm
thanks for your reply
So is the fact that in the UI it's not showing as 2008 compatibility a problem? I'm guessing that since sysdatabases shows it correctly then it's fine?
I do want to change them all, migrating from a 2005 box to a new box with 2008. First upgrading some VMs by restoring backups, my prod change will be to detach and reattach dbs.
November 16, 2012 at 1:41 am
Well, sp_dbcmptlevel is deprecated, so is sysdatabases.
ALTER DATABASE ... SET COMPATIBILITY LEVEL and sys.databases.
If you're using 2005 management studio to connect, it's not able to show compat mode 100, because that's newer than the tool is.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply