Sql-server – Unable To Restore Database From .BAK File

sql serversql-server-2016

I am trying to restore my .bak file but I keep getting the error

Restore of database 'DataWarehouse' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
System.Data.SqlClient.SqlError: Restore Database is terminating abnormally (Microsoft.SqlServer.SmoExtended)

Is this a corrupt .bak file I am trying to restore from?

The steps I took were

1) Right clicked on the server name and chose restore
2) Navigated to the location of the .bak file

3) Clicked Ok

Best Answer

Perform the following commands in SQL Server Management Studio (SSMS) on the SQL Server instance you are trying to restore the database to.

Verify BAK File

RESTORE VERIFYONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

The backup set on file 1 is valid.

Possible Errors

If the check terminates abnormally then the cause could be because one of these checks have failed:

Checks performed by RESTORE VERIFYONLY include:

  • That the backup set is complete and all volumes are readable.
  • Some header fields of database pages, such as the page ID (as if it were about to write the data).
  • Checksum (if present on the media).
  • Checking for sufficient space on destination devices.

Reference: RESTORE Statements - VERIFYONLY (Transact-SQL) (Microsoft | SQL Docs)

Check the BAK Fileheader

RESTORE HEADERONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

BackupName  BackupDescription     BackupType ExpirationDate Compressed Position DeviceType UserName                         ServerName    DatabaseName         DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN              LastLSN               CheckpointLSN         DatabaseBackupLSN     BackupStartDate         BackupFinishDate        ........
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ........
NULL        NULL                  1          NULL           0          1        2          NT SERVICE\SQLSERVERAGENT        NOTHING       StackExchange        782             2019-07-24 09:17:00.000 1688399872  568000013810400037    568000013813600001    568000013810400037    568000013524000037    2020-01-15 19:25:27.000 2020-01-15 19:25:29.000 ........

Check the Contents of the BAK File

RESTORE FILELISTONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

LogicalName              PhysicalName                                   Type FileGroupName    Size         MaxSize         FileId  CreateLSN           DropLSN  UniqueId                             ReadOnlyLSN         ReadWriteLSN       BackupSizeInBytes  SourceBlockSize FileGroupId LogGroupGUID  DifferentialBaseLSN  DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint 
------------------------ ---------------------------------------------- ---- ---------------- ------------ --------------- ------- ------------------- -------- ------------------------------------ ------------------- ------------------ ------------------ --------------- ----------- ------------- -------------------- ------------------------------------ ---------- --------- --------------
StackExchange            C:\SQL\SQL_DATA\StackExchange.mdf              D    PRIMARY          2623537152   35184372080640  1       0                   0        725120E9-8DF3-4E09-9A89-94433A6EB49B 0                   0                  1687224320         4096            1           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_DefRO      C:\SQL\SQL_DATA\StackExchangeRO.ndf            D    DEFAULTRO        5242880      35184372080640  3       89000000045300001   0        05C6BA44-123A-4749-8FB5-A0A16EF49A51 102000000014600005  90000000013100004  131072             4096            2           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 1          1         NULL
StackExchange_PUBL_SNAP  C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf    D    PUBL_SNAP_DBASE  5242880      35184372080640  4       286000001633700001  0        DCA761C4-6260-4BE3-8280-64099C98911D 0                   0                  131072             4096            3           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_log        C:\SQL\SQL_LOGS\StackExchange_log.ldf          L    NULL             21798322176  2199023255552   2       0                   0        364124B1-081E-45C5-971C-2CD1CBBF13EC 0                   0                  0                  4096            0           NULL          0                    00000000-0000-0000-0000-000000000000 0          1         NULL

If the Steps Fail

If any of the above steps fail, then you will have to determine what the issue is. The RESTORE ... command should provide enough information to assist you in finding the root cause.

Example Solutions

If the backup file is corrupt, then you will have to find a backup file that works.
If the disk is full, then you will have to add additional space.
If all else fails, then you might have to open a case with Microsoft Support.

Best Practice

(emphasis mine)

Test your backups!

You do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. It is also recommended that once you restore the backup, you perform database consistency checks via DBCC CHECKDB of the database to validate the backup media was not damaged.

Reference: Back Up and Restore of SQL Server Databases (Microsoft | SQL Docs)

Related Question