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.
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.