Often people have corruption issues regarding
Microsoft's SQL server database as well as its backup. Basically, backup is the
only way to make your database secured and protective; it helps you to restore
your inaccessible files of the main database when any type of corruption or
damage occurs. But, what if backup also corrupt, while attempting to restore
database from backup file. There could be multiple reasons behind such disaster
situation, here in this write-up you will get to know about the reason and
solutions behind such case, where SQL Server user faces corruption in SQL
Server database backup and not able to restore their databases.
How and why the backup file gets corrupt:
SQL backup files are basically a replica of your
original SQL database, which can be located in different locations on the
system. There could be multiple reasons of inaccessible backup file. Here are
some most common causes of damaged SQL BAK files:
- Virus attack
- Abrupt system shutdown
- Use of a wrong driver
- Bad sectors in your system's hard disk
- Sudden removal of a selected tables,
records, and procedures
- unconventional functioning of Hard disk
- Improper shutdown of application
- Wrong database synchronization
- System crash
- corrupt database system rules and tables
The most common error message during restoration of
database is: 'Backup or restore
operation terminating abnormally.'
A Backup restoration error occurs when a filemark in
the backup device could not be read. There could be multiple causes of when a
user encounters a filemark error. The most common reasons are:
- A media failure may arise on the same device where the
backup is stored
- A write failure may occur while creating the backup
file
- Loss of connectivity may arise while creating a
network backup
- A failure in the Input/Output path occurs in the disk
just after successful write to the disk
Manual Solution:
After backup restore error the first thing you could
do is to check whether all the sets of backup have issues or just some sets
have issues. It might be possible that only some sets of backup have issues due
to which you are getting restore error. In order to retrieve other backup sets
from the device, you need to specify the file number. In case, there are
multiple backup sets available on a single device, then to determine the usable
backup, you can run the following query:
RESTORE HEADERONLY
FROM DISK='<Backup Location>'
If you got the usable set from the disk, copy it to another drive for
usage and try to restore the damaged files with the help of SQL restore
commands. Here are some of the SQL commands that you can use to restore
corruption in your SQL database backup.
To recover
a database use the following command. This will put your database in the
"restoring" state
RESTORE DATABASE
<DB Name> FROM DISK='<Backup Location>' WITH FILE = <FileNumber>
Note: Write the backup set number instead of 'FileNumber'
that you want to restore.
The following command will take the database, which is in 'restoring'
state and make it available for end users.
RESTORE LOG
<DB Name> FROM DISK = '<Backup Location>'
WITH RECOVERY
The above mentioned commands are used to restore
corrupt backup file of SQL database. However, these corrupt backup recovery
solutions provided by Microsoft are not applicable for deep corruption cases.
In order to restore your highly damaged or corrupt SQL backup database you can
always choose a third party SQL backup recovery software. These professional
utilities are designed to restore data from a corrupt (.BAK) SQL backup
file.
Third party applications have functions to restore SQL
backup file due to all above mentioned reasons. Before buying any professional
backup recovery tool, you need to choose the most reliable one. For that you
should use the online demo versions of the backup recovery applications to test
their efficiency.
Jyoti is a Sr. DBA - SQL Server at Stellar Data Recovery and has written several article on SQL Server disaster recovery planning & fixing. In addition, she spend her time on Technical forums helping people with the issues related to SQL server.
