Restore looks like it ran but it did not.

poppo101

we have a customer that is using SQL Backup to backup a database nightly on one server and then restore it on another server that is used to run reports. The backup part works flawlessly creating the backup file on the reports server. The restore on the reports server however, doesn't always work. The most recent time that it did work, it worked for three days. Since then, it looks like it ran ok, it only shows having run for 3 seconds or so instead of the normal one hour 45 minutes +. No errors show up at all. The other thing that sometimes happens is that the restore runs the normal amount of time and shows that it was successful, but the database still shows that it is loading. In this case, they need to shutdown and restart SQL Server and then rerun the restore. This has become frustrating for them since they are becoming more reliant on having the reports database available to them.

Process used nightly is as follows:

Production server-

8:30PM use SQL Backup to backup log file - runs 15-20 minutes

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP LOGS [Heritage] TO DISK = ''\\admincentreport\BACKUP\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, ERASEFILES_ATSTART = 1, COMPRESSION = 3, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

9:30PM us SQL Backup to backup database - runs 45-50 minutes

DECLARE @exitcode int
DECLARE @sqlerrorcode int
exec master..sqlbackup N'-SQL "BACKUP DATABASES [Heritage] TO DISK = ''\\admincentreport\BACKUP\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', INIT, ERASEFILES_ATSTART = 1, COMPRESSION = 3, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

On reports server -

11:00PM - restore database using SQL Backup - runs approx. 1 hour 45 minutes

master..sqlbackup N'-SQL "RESTORE DATABASE [Heritage] FROM DISK = ''D:\Program Files\MSSQL\BACKUP\FULL_(local)_Heritage_*.sqb'' WITH RECOVERY, REPLACE, ERASEFILES = 1, FILEOPTIONS = 1, MOVETO = ''D:\Program Files\MSSQL\BACKUP\processed""'



Log file backup is not restored.

petey

The task to restore the database is not set up to raise errors. Try modifying it to the following:

DECLARE @exitcode int 
DECLARE @sqlerrorcode int 

exec master..sqlbackup N'-SQL "RESTORE DATABASE [Heritage] FROM DISK = ''D:\Program Files\MSSQL\BACKUP\FULL_(local)_Heritage_*.sqb'' WITH RECOVERY, REPLACE, ERASEFILES = 1, FILEOPTIONS = 1, MOVETO = ''D:\Program Files\MSSQL\BACKUP\processed""', @exitcode OUTPUT, @sqlerrorcode OUTPUT 

IF (@exitcode <>0) OR (@sqlerrorcode <> 0) 
BEGIN 
  RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) 
END

My guess is that when the job runs for only 3 seconds, the restore actually failed because the database was still in use. Subsequently, during the next restore, the command would have picked up the old file together with the latest file, and would also have failed as they should not be restored as a set. You can check the log files, default location is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>

I would suggest incorporating a script to kill off all connections to the database prior to the restore, if its acceptable. Also, when the database is in a loading state, you can change its state to online by running the folllowing:

RESTORE DATABASE Heritage WITH RECOVERY

I do not know how the database could have gone into a recovery state, using the commands you provided. Could you pls run a Profiler trace on the database, to trace all RESTORE commands? Thanks.