Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Upgrade Database from SQL Server 2005 to 2008 R2 using Restore w/StandBy Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 1:23 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1383849
Posted Monday, November 12, 2012 1:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1383850
Posted Monday, November 12, 2012 1:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1383852
Posted Monday, November 12, 2012 1:29 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 16, 2014 1:12 PM
Points: 204, Visits: 1,520
You have to recover the database to allow the upgrade to complete. You could however immediately set the database to read-only.
Post #1383853
Posted Monday, November 12, 2012 1:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1383854
Posted Monday, November 12, 2012 1:40 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:57 PM
Points: 4,242, Visits: 4,290
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/

Post #1383860
Posted Thursday, November 15, 2012 3:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:06 PM
Points: 3, Visits: 15
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.
Post #1385379
Posted Thursday, November 15, 2012 4:13 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1385392
Posted Thursday, November 15, 2012 4:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:06 PM
Points: 3, Visits: 15
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.
Post #1385395
Posted Friday, November 16, 2012 1:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1385534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse