InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:15:46 GMT20http://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxWell, 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.Fri, 16 Nov 2012 01:41:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxthanks for your replySo 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.Thu, 15 Nov 2012 16:17:04 GMTbez7-793740http://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxYou 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.Thu, 15 Nov 2012 16:13:20 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxSo 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.Thu, 15 Nov 2012 15:20:44 GMTbez7-793740http://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxok, thanks again.Mon, 12 Nov 2012 13:40:53 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspx[quote][b]Welsh Corgi (11/12/2012)[/b][hr]After I restore with recovery what steps are required to upgrade other then setting the Compatibility Level?[/quote]None are required, not even setting the compat mode is required.You probbaly want to run a checkDB, update stats.Mon, 12 Nov 2012 13:32:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxYou have to recover the database to allow the upgrade to complete. You could however immediately set the database to read-only.Mon, 12 Nov 2012 13:29:34 GMTRichard Moore-400646http://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxok, thanks Gail.After I restore with recovery what steps are required to upgrade other then setting the Compatibility Level?Thanks again.Mon, 12 Nov 2012 13:29:01 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxCan't be done.In an upgrade setup, the database can only be restored with NORECOVERY or WITH RECOVERY (which ends the restore sequence)Mon, 12 Nov 2012 13:25:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1383849-1550-1.aspxI 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.[url]RESTORE DATABASE BISupportFROM 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'[/url]I get the following error:[quote]Msg 3180, Level 16, State 1, Line 1This 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 1RESTORE DATABASE is terminating abnormally.[/quote]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.Mon, 12 Nov 2012 13:23:13 GMTWelsh Corgi