SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgrade Database from SQL Server 2005 to 2008 R2 using Restore w/StandBy


Upgrade Database from SQL Server 2005 to 2008 R2 using Restore w/StandBy

Author
Message
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27008 Visits: 4895
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/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224801 Visits: 46321
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

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


Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27008 Visits: 4895
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/
Richard Moore-400646
Richard Moore-400646
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 1640
You have to recover the database to allow the upgrade to complete. You could however immediately set the database to read-only.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224801 Visits: 46321
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

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


Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27008 Visits: 4895
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/
bez7-793740
bez7-793740
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224801 Visits: 46321
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

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


bez7-793740
bez7-793740
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224801 Visits: 46321
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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search