• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Problem with Simple Log Shipping


I have the following problem. I am trying to log ship between two standard SQL 2000 servers. I have installed SP_ShipLog on the primary server, in the master database, and sp_applystandbylog on the secondary server in the master database.

When I run the sp_shiplog from the primary server, the log.bak gets copied correctly to a share on the secondary server. However, when the job on the primary server trys to apply the log to the secondary server's database (which is read-only and set up accept the application of transaction logs), I get the following message:

Executed as user: domain1\troutSQL. ...roup\mssql\TransLogs\TEST_20060525_1037_LOG.BAK [SQLSTATE 01000] (Message 0)  BACKUP LOG successfully processed 0 pages in 0.372 seconds (0.000 MB/sec). [SQLSTATE 01000] (Message 3014)  

Execute [SQLServer01].master.dbo.sp_ApplyStandByLog N'Yoda',N'\\SQLServer01\data\data_srv\dbagroup\mssql\TransLogs\Yoda_20060525_1037_LOG.BAK',N'\\SQLServer01\data\data_srv\dbagroup\mssql\TransLogs\undo_yoda.dat' [SQLSTATE 01000] (Message 0)  

Executing RESTORE LOG Yoda FROM DISK='\\SQLServer01\data\data_srv\dbagroup\mssql\TransLogs\Yoda_' WITH STANDBY='\\SQLServer01\data\data_srv\dbagroup\mssql\TransLogs\undo_yoda.dat [SQLSTATE 01000]
(Message 0)  RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013)  

Cannot open backup device '\\SQLServer01\data\data_srv\dbagroup\mssql\TransLogs\Yoda_'.
Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201)  Error severity levels greater than 18 can...  The step failed.

1 Solution
Try restoring the database on teh secondary server again using the WITH NORECOVERY option ...   this will leave teh seconday db in loading state ..

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now