Restore keeps failing

Question

I have a SQL Server 2014 instance with a ~900GB backup database that goes through a weekly restoration process from a full backup. The server it's on is a Windows Server 2012 R2.

I have recently started seeing the database fail to be restored each weekend.

There are 3 backup files that are copied across to the backup site, produced on the Sat evening around 8, taking only about an hour, copied down to this server just before midnight (usually taking 1-2 hours per file) then restored from at 6am the following morning.

I notice one of the backup files consistently has a date modified time of Sun 6:50, but the other two have it as Sat ~9pm. When attempting to restore to a dev server to verify the backup media it takes an exceptionally long time to read the backup device header for that file.

I have been and still currently am sifting through google results to see if I can work out an answer as well, but no luck so far. Does anyone have any suggestions of what I should be checking or ideas on what the problem might be?

The restore job is done from a procedure called by a PowerShell script. This is the email error I received the last Sunday morning for example:

Sent: 30 October 2016 12:01 To: email Subject: Server1 - Database Restore Failure

The following database(s) have not restored correctly on Server1 - please investigate.

Database1

The log file of the copy task just shows the following:

10/30/2016 00:07:35 Copying databases matching *_database1_FULL_*.bak

The log file for restore task shows:

10/30/2016 08:29:33 Restoring Database1 From \sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak 10/30/2016 08:29:33 Restoring Database2 From \sharedfileserver\folder\AvailabilityGroup1_Database2_FULL_20161029_201026_1.bak 10/30/2016 08:29:33 5 percent processed. (some time elapses...) 10/30/2016 08:34:19 100 percent processed. 10/30/2016 08:34:19 Processed 4946928 pages for database 'Database2', file 'DATABASE2' on file 1. 10/30/2016 08:34:19 Processed 21 pages for database 'Database2', file 'DATABASE2_log' on file 1. 10/30/2016 08:34:27 RESTORE DATABASE successfully processed 4946949 pages in 303.526 seconds (127.330 MB/sec). 10/30/2016 08:34:27
10/30/2016 08:34:27 3013 - RESTORE DATABASE is terminating abnormally. 10/30/2016 08:34:27 -1 10/30/2016 08:34:27 Done. 10/30/2016 08:34:28 Finished execution

Note: It's Database1 that's the problem child.

Answer 1

I would verify the integrity of your backup files using the following commands.

Restore HEADERONLY

SQL BOL: Returns a result set containing all the backup header information for all backup sets on a particular backup device.

RESTORE HEADERONLY FROM DISK ='\\sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak'

You might find some information why your backup file is not working.

Restore FILELISTONLY

SQL BOL: Returns a result set containing a list of the database and log files contained in the backup set.

RESTORE FILELISTONLY FROM DISK = '\\sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak'

There was a rare occasion where the logical filename of the database was truncated in SQL Server 2008 R2

You might find more information in the ERRORLOG of the server you are restoring to. Otherwise I would consider running the restore script outside of the scheduled task in Query Window to catch any other errors.