DB restoration Issues in sql

Category: sql server manageability


Hemadribabu on Wed, 28 Mar 2018 12:32:39


The Backup file size is 785 GB .

I am trying to restore with SSMS , and i have been waiting  for 4 hours but i couldn't see the data set displayed as shown in the below picture . 

I tired with Queries too, but it ends up with some strange error s. 

The backup data at the end of "D:\DB_BACKUP\HRMS_Publish_DB.bak" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
2018-03-27 18:50:46.58 Backup      Error: 18210, Severity: 16, State: 1.
2018-03-27 18:50:46.58 Backup      BackupIoRequest::ReportIoError: read failure on backup device 'D:\DB_BACKUP\HRMS_Publish_DB.bak'. Operating system error 13(failed to retrieve text for this error. Reason: 15100).
2018-03-27 19:31:13.15 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2018-03-27 19:31:13.92 spid12s     Service Broker manager has shut down.
2018-03-27 19:31:13.92 spid12s     Error: 17054, Severity: 16, State: 1.
2018-03-27 19:31:13.92 spid12s     The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.

Note: i am sure that the backup file is not corrupted , as i tested 

your view please

thank you 




TiborK on Wed, 28 Mar 2018 13:29:21

1: Skip the GUI. for these types of issues, you don't want to involve another unknown in the mix (the GUI). I.e., run TSQL commands from a query window.

2: Execute RESTORE HEADERONLY FROM DISK = 'D:\DB_BACKUP\HRMS_Publish_DB.bak' and let us know what result or error you get.

It might be that the backup is from a higher version of SQL Server, but we need more info. Above is a good start.

Tom Phillips on Wed, 28 Mar 2018 14:03:06

That error is almost always an indication either the file is corrupt, or you are trying to restore a file generated with a higher version of SQL Server to a much lower version.  SQL Server does not support downgrading a database.

Please verify the version of the source and the target database engine.

Jinu Varghese on Wed, 28 Mar 2018 23:36:09


I have faced this situation many times but there can be differences in the situation and reason for the failure. I thin you have a SQL 2005 server(wild guess based on the error description)

  • the backup you are using might be from a Higher SQL version and the target is below that. SQL engine doesn't allow that.
  • A filemark in the backup device could not be read. There are many reasons why you may encounter a filemark error. Some of the reasons include the following :
    1) A media failure may occur on the device where the backup is located.
    2) A write failure may occur during the creation of the backup.

For example, a loss of connectivity may occur during a network backup. Or, a failure of the IO path to flush the write to disk may occur after the write to disk was reported to SQL server as successful.

So as Tibor requested, please let us know the output for "RESTORE HEADERONLY "