Restore Errors
jkz999
Posts: 9
Hello
We have an issue with restoring log backups to a server. The scenario is this. We backup our databases with the following command:
(Edited to take out servername and email domains)
As you can see there are two COPYTO steps, both of which appear to be working. That is, the log backup files end up where we expect them to be.
This is the basis of how we log ship to two servers.
The DR_Server is remote and has no problem restoring the files. It is working exactly as we want it to.
The problem is with what we call our OLAP server. This server is scheduled to run the job to restore logs from 19:00 hrs to 5:00 hrs, and then be available as a read only copy of yesterdays transaction for people to query. As we do not restore through the day, (as this kicks people out), at 19:00 it has a days worth of logs to catch up on.
This is a very hit and miss affair. This mornings log (Generated by the 19:00 Job last night) looks like:
As you can see the restore ran for 14 minutes before failing.
The physical file name 'D:\MSSQL\Data\LIVE_DB_1.MDF' exists and is on a partition with 85 Gb of space. (The DB is around 100 Gb plus log). It is the expected size.
The differences between the DR and OLAP servers are
a. The DR server loads from local copies. The OLAP server loads from a UNC path which is a server in the same comms room.
b. The DR applies logs constantly as it receives them, the OLAP box restores a days worth of logs in one hit.
The databasse is now in Restoring/Suspect mode. This means I need to do a full restore to get it back and then re-apply logs manually.
All of which is do-able but it takes time and is a royal pain when the users, who by definition are the top people in the company, complain that the db is not there to query.
I am considering not log shipping to this box at all, but simply doing a full restore each night. The concern is if the log backups which are being loaded from a UNC are failing, how reliable will the full ones be?
Any thoughts?
Dave Jackson
We have an issue with restoring log backups to a server. The scenario is this. We backup our databases with the following command:
DECLARE @errorcode INT DECLARE @sqlerrorcode INT EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [LIVE_DB] TO DISK=''S:\MSSQL\TLogBackup\LIVE_DB\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COMPRESSION = 1, ERASEFILES = 120h, MAILTO_ONERROR = ''Development-IT@nowhere.com'', COPYTO = ''\\DR_SERVER\TLogBackup\Live_DB\'', COPYTO = ''\\FILE_SERVER\mssql$\Live_Server\Logs\LIVE_DB''"', @errorcode OUT, @sqlerrorcode OUT;
(Edited to take out servername and email domains)
As you can see there are two COPYTO steps, both of which appear to be working. That is, the log backup files end up where we expect them to be.
This is the basis of how we log ship to two servers.
The DR_Server is remote and has no problem restoring the files. It is working exactly as we want it to.
The problem is with what we call our OLAP server. This server is scheduled to run the job to restore logs from 19:00 hrs to 5:00 hrs, and then be available as a read only copy of yesterdays transaction for people to query. As we do not restore through the day, (as this kicks people out), at 19:00 it has a days worth of logs to catch up on.
This is a very hit and miss affair. This mornings log (Generated by the 19:00 Job last night) looks like:
20/11/2007 19:14:54: SQL Server error SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally. SQL error 3167: SQL error 3167: RESTORE could not start database 'LIVE_DB'. SQL error 5105: SQL error 5105: Device activation error. The physical file name 'D:\MSSQL\Data\LIVE_DB_1.MDF' may be incorrect. Deleting database file 'C:\MSSQL\BACKUP\UNDO_LIVE_DB.dat'. Processed 1947289 pages for database 'LIVE_DB', file 'LIVE_DB_Log' on file 1. 20/11/2007 19:14:55: Deleting old backup file: \\FILE_SERVER\mssql$\DB_Server\Logs\LIVE_DB\Processed\LOG_LIVE_DB_20071115053503.sqb 20/11/2007 19:14:55: Deleting old backup file: \\FILE_SERVER\mssql$\DB_Server\Logs\LIVE_DB\Processed\LOG_LIVE_DB_20071115060503.sqb <snip>
As you can see the restore ran for 14 minutes before failing.
The physical file name 'D:\MSSQL\Data\LIVE_DB_1.MDF' exists and is on a partition with 85 Gb of space. (The DB is around 100 Gb plus log). It is the expected size.
The differences between the DR and OLAP servers are
a. The DR server loads from local copies. The OLAP server loads from a UNC path which is a server in the same comms room.
b. The DR applies logs constantly as it receives them, the OLAP box restores a days worth of logs in one hit.
The databasse is now in Restoring/Suspect mode. This means I need to do a full restore to get it back and then re-apply logs manually.
All of which is do-able but it takes time and is a royal pain when the users, who by definition are the top people in the company, complain that the db is not there to query.
I am considering not log shipping to this box at all, but simply doing a full restore each night. The concern is if the log backups which are being loaded from a UNC are failing, how reliable will the full ones be?
Any thoughts?
Dave Jackson
Comments
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8