SQL Server Performance Forum – Threads Archive

Log Ship from SQL 7.0 to SQL 2000

Hi All, I am trying to restore a database backup from SQL Server 7.0 to SQL Server 2000. I want to use the SQL Server 2000 database for log shipping and eventually cutover to the the 2000 server. When I try to restore the backup from 7.0 using the WITH STANDBY option I get the following error: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. I know I can get the database restored by taking out the WITH STANDBY option. However I want to use the 2000 database for log shipping and therefore want it in standby mode. I also know that once I restore the database I can make a backup of the SQL 2000 database and restore it WITH STANDBY. However I don’t want to utilize all the extra time it takes to backup and restore again. Does anyone know how I can restore to SQL 2000 WITH STANDBY or how I can easily place the database in standby mode after it is restored? Update: After I wrote the original text above I tried restoring the 7.0 database, making a backup of the SQL 2000 database and restoring it WITH STANDBY. This put it into STANDBY mode. However I could not log ship to the server from SQL 7.0 Basically I am looking for a solution which would allow me to make an incremental (daily) restore from SQL 7.0 to SQL 2000 AND perform log shipping from SQL 7.0 to SQL 2000 during the day. Thanx.
Jeff

For the first instance you must follow the path of RESTORE database using without WITH STANDBY clause and then after for every transaction log restore you can use the WITH STANDBY clause. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Thank you for the reply. I tried what you suggested but it is not working. The DB restore works properly but the Log restore is failing with the following error message: Server: Msg 4306, Level 16, State 1, Line 3
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
I first backup the log on SQL 7.0. I copy the .bak file to my SQL 2000 box and use the code below to restore the log: EXEC sp_dboption databasename, ‘single user’, true
WAITFOR DELAY ’00:00:05′
RESTORE LOG databasename
FROM DISK = ‘r:mssqlackupLogShippingdatabasename_LOG.BAK’
WITH
STANDBY = ‘c:mssqlstandbydatabasename_standby.ldf’,
DBO_ONLY
WAITFOR DELAY ’00:00:05′
EXEC sp_dboption databasename, ‘dbo use only’, false
EXEC sp_dboption databasename, ‘single user’, false I tried running the above SQL with NORECOVERY, without DBO_ONLY and other variations but cannot get it to work. Any help you can provide will be appreciated. Thanx. Jeff

Jeff I suggest to restore the full database backup from source to target using WITH NORECOVERY clause and then try pumping out the transaction log backups, and I’m sure this will work in order to the keep the log shipping process alive. Stop the transaction log backup job on SQL 7 server before executing the full backup and until the full database backup is restored on the secondary server, once restore process is finished then take the next set of transaction log backps. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Thank you for the recommendation. However this does not work either. I tried restoring the DB with NORECOVERY and this is successful. When this completes the DB status is "Loading" and I cannot see any objects in the DB. I have tried many variations of the Restore (With NORECOVERY) SQL with no success. Below is one variation of the SQL I used to do this is: EXEC sp_dboption databasename, ‘single user’, true
WAITFOR DELAY ’00:00:05′
RESTORE DATABASE NobleHouse
FROM DISK = ‘r:mssqlackupLogShippingdatabasename.BAK’
WITH
NORECOVERY
WAITFOR DELAY ’00:00:05′
EXEC sp_dboption NobleHouse, ‘dbo use only’, false
EXEC sp_dboption NobleHouse, ‘single user’, false The results from the above SQL are:
Processed 1856 pages for database ‘databasename’, file ‘databasename_Data’ on file 1.
Processed 1 pages for database ‘databasename’, file ‘databasename_Log’ on file 1.
RESTORE DATABASE successfully processed 1857 pages in 0.297 seconds (51.196 MB/sec). However as I stated above the DB is in a Loading state and I cannot see any objects.
When I then try to restore the log with the code in my previous reply I get the following error messages: Server: Msg 927, Level 14, State 2, Line 1
Database ‘databasename’ cannot be opened. It is in the middle of a restore.
Server: Msg 3180, Level 16, State 1, Line 3
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally. I tried recovering the DB after restoring with NORECOVERY. That went okay but when I tried to restore the log I get the following error messages:
Server: Msg 4306, Level 16, State 1, Line 3
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Once again any information anyone can provide would be helpful. Samples of your RESTORE statements for the 7.0 backup to 2000 and your RESTORE LOG statements for 7.0 logs to 2000 would be extremely helpful. Jeff

Its wierd to see why it is not working, as I don’t have any issues at my end. Review contents from thishttp://www.windowsitpro.com/SQLServer/Article/ArticleID/9808/9808.html article about STANDBY options. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes this is very strange indeed. I read the article. Unfortunately it did not provide any insight into the cause of the issues I am having. If anyone has any suggestions or ideas please let me know.

]]>