March 1, 2011 at 2:19 pm
ALL,
INFO - I've got a brand new server and am trying to figure out the best way to switch to this new server while the other one stays up and runing. The plan is to copy the '05 DB's over to the '08 instance, restore the latest DIFF backups, then restore the TRN log files. Ultimately I'd like to then set up log shipping from the '05 server to the '08 so i can keep the new server up to date BEFORE taking the old one offline.
WHAT I'VE TRIED - I've tried taking the latest FULL '05 db backup from the SAN and restoring it to the new '08 server (successful). However, when I attempt to set it up for LOGSHIPPING so I can restore the DIFF backup and TRN files:
RESTORE DATABASE [MyDB] FROM DISK = N'\\SAN\SQL_Backups\MYServer\MyDB\MyDB_backup_201102281747.bak'
WITH FILE = 1, MOVE N'mydb' TO N'D:\SQL\DATA\mydb.mdf',
MOVE N'mydb_log' TO N'T:\SQL\LOG\mydb_log.ldf',
STANDBY = N'F:\SQL\LOGSHIPPING\ROLLBACKFILES\mydb\mydb_DisasterRecovery_UNDO.BAK',
NOUNLOAD, REPLACE, STATS = 10
GO
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've read a few articles online that suggest trying the COPY DATABASE wizard from MGMT Studio but that results in errors galore, specifically when attempting to add roles:
OnError,MyServer,MYDOMAIN\myuser,MyServer_Transfer Objects Task,{EC25BCEE-D5A3-42CA-8BA4-DC7415033564},{A83C58A3-CA93-4630-9DC0-9D67144CAE45},3/1/2011 2:45:13 PM,3/1/2011 2:45:13 PM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "sys.sp_addrolemember @rolename = N'sp_Reports', @m..." failed with the following error: "The role 'sp_Reports' does not exist in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
I've tried setting the DB compatibility level to SQL Server 2008 (100) but that doesn't help...but haven't tried setting it on the SOURCE server in fear of what "could" happen.
What am I missing? Is there a relatively simple way to "upgrade" an '05 db to '08, so i can set up logshipping from the source to destination server?
Will changing the DB compatibility level on the source server ('05) solve anything?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 1, 2011 at 8:04 pm
Serge Mirault (3/1/2011)
RESTORE DATABASE [MyDB] FROM DISK = N'\\SAN\SQL_Backups\MYServer\MyDB\MyDB_backup_201102281747.bak'WITH FILE = 1, MOVE N'mydb' TO N'D:\SQL\DATA\mydb.mdf',
MOVE N'mydb_log' TO N'T:\SQL\LOG\mydb_log.ldf',
STANDBY = N'F:\SQL\LOGSHIPPING\ROLLBACKFILES\mydb\mydb_DisasterRecovery_UNDO.BAK',
NOUNLOAD, REPLACE, STATS = 10
GO
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.
What am I missing? Is there a relatively simple way to "upgrade" an '05 db to '08, so i can set up logshipping from the source to destination server?
Will changing the DB compatibility level on the source server ('05) solve anything?
My understanding is that if you want to do log shipping from 2005 to 2008 you have to use the NORECOVERY option. (You can't use the STANDBY option.)
So this should get you going:
RESTORE DATABASE [MyDB] FROM DISK = N'\\SAN\SQL_Backups\MYServer\MyDB\MyDB_backup_201102281747.bak'
WITH FILE = 1, MOVE N'mydb' TO N'D:\SQL\DATA\mydb.mdf',
MOVE N'mydb_log' TO N'T:\SQL\LOG\mydb_log.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
The reason for this is that to access the database on 2008 it has to be upgraded to the 2008 format, and once that is done you can no longer restore 2005 transaction logs to it. So you can setup log shipping, but you won't be able to access the database until you stop the log shipping and upgrade the database.
Changing the database compatibility mode won't help.
Using the database copy wizard wouldn't allow you to setup log shipping, so I'm not sure you want to try to resolve that problem.
March 1, 2011 at 9:31 pm
Hey thanks for the reply!!! I realize you can use the NORECOVERY option but that keeps the DB in a mode where we can't test anything as the DB's are all inaccessbile 🙁 Sounds like you're telling me there's not going to be a way to do this...which irritates me to no end because the MS error makes it sound like all that's needed is the "wonder tool" that upgrades a DB from '05 to '08 (which if it exists, I'm not able to find it.
"The reason ... but you won't be able to access the database until you stop the log shipping and upgrade the database."
2 questions:
1) When I get to the point where I want to stop logshipping, How do I upgrade the database? Or is it already upgraded?
2) I am not familiar with taking databases out of NORECOVERY mode, is it just a simple matter of restoring the very last TRN file WITH RECOVERY???
"Using the database copy wizard wouldn't allow you to setup log shipping, so I'm not sure you want to try to resolve that problem."
I read on BOL that using the DB Copy Wizard would automatically upgrade the databases from '05 to '08...however, in trying this with the DETACH method, I didn't see anything that would lead me to believe it worked...
Thanks for all your help!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 2, 2011 at 10:23 am
Serge Mirault (3/1/2011)
Hey thanks for the reply!!! I realize you can use the NORECOVERY option but that keeps the DB in a mode where we can't test anything as the DB's are all inaccessbile 🙁 Sounds like you're telling me there's not going to be a way to do this...which irritates me to no end because the MS error makes it sound like all that's needed is the "wonder tool" that upgrades a DB from '05 to '08 (which if it exists, I'm not able to find it."The reason ... but you won't be able to access the database until you stop the log shipping and upgrade the database."
2 questions:
1) When I get to the point where I want to stop logshipping, How do I upgrade the database? Or is it already upgraded?
2) I am not familiar with taking databases out of NORECOVERY mode, is it just a simple matter of restoring the very last TRN file WITH RECOVERY???
Yes, you just restore the last TRN file WITH RECOVERY and it will upgrade the DB and bring it online. Alternatively if the last TRN has been restored with NORECOVERY you can initiate the recovery this way:
RESTORE DATABASE database_name WITH RECOVERY
"Using the database copy wizard wouldn't allow you to setup log shipping, so I'm not sure you want to try to resolve that problem."
I read on BOL that using the DB Copy Wizard would automatically upgrade the databases from '05 to '08...however, in trying this with the DETACH method, I didn't see anything that would lead me to believe it worked...
The DB Copy Wizard would make a new database that would be in the 2008 format. If you DETACH the DB on 2005, copy it to the 2008 server and ATTACH it, SQL Server will automatically convert/upgrade the DB. This is a one-way process, once a DB has been upgraded it can't ever be put back on 2005 either through DETACH/ATTACH or BACKUP/RESTORE. (However I think you can still use the DB Copy Wizard to copy it back.)
I would suggest that you do a backup of the 2005 DB and restore it on 2008, then do all of your testing. If everything tests OK, then you can start over with log shipping so that you can shut-down the 2005 and bring up the 2008 with minimal downtime.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply