Summary: SQL Server Administrator is at his wit’s end. SQL Backup scripts have been working fine till last two days when suddenly the backup terminates and the SQL database error 3013 is witnessed by the Administrator.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally
Probable Reasons for SQL database error 3013
Sometimes, when Administrator is performing database backup, SQL database error 3013 is displayed. Following reasons attribute to the occurrence of this error:
- A write failure has occurred: When there is not enough storage on the backup drive. Normally, compression occurs after the backup is completed. This means, logically backup drive should have double space
- A media failure has occurred: Storage device with backup has encountered a media failure.
- Attempt to unauthorized access to view the Network drive has occurred
- Database in SUSPECT mode and attempt made to execute backup on transactional logs
SQL Errors are OS and Version specific
The occurrence of the error is dependent on versions of SQL application and/or Operating system as explained below:
- SQL Server 7.0: SQL database error 3013 occurs when a clustered index is created in every filegroup on the table
- SQL Server 2000: If the database less of volume lesser than 2 GB is already available and another database with volume of more than the existing volume is tried to be backed up, then it results in the specified error
- SQL Server 2005: Error occurs when Backup Admin tries to restore data files and log files together at a single instance of time.
Troubleshoot as per the problem
As the error is Version and OS specific hence resolution steps should also be based as per the version and Operating system. Since backup has terminated abruptly, it is no use rewriting the same backup as it may result in same error again.
Check the security permission for user. To do so:
- Go to the location of the backup folder where you have stored the .BAK file
- Right click on the backup file and select Properties
- Now check the Deny permission for Authenticated users
- Click on Edit and remove the Deny
Manually delete the previous backup and enable SQL server to execute new backups to the backup device. Use the following command for manual deletion of the last backup:
“BACKUP DATABASE mydatabase TO DISK= ‘C:\Mydatabase. Bak’ with format”
This may resolve the problem provided the error has not occurred during backup restoration.
If backup restoration is the cause for the incidence of this error, file number can be specified to retrieve the consecutive sets as available within the backup device. Run the following command to retrieve the backup from Query Analyzer:
“RESTORE HEADERONLY FROM DISK=’C: \MyDatabase.bak”
But you need to specify the particular backup set for retrieval. Use the following command too
“RESTORE DATABASE mydatabase FROM DISK=’C: \MyDatabase.bak WITH FILE = FileNumber”
File number signifies the “backup set series” that needs to be restored.
Sometimes, partial restoration is not the solution, then the only option is to perform full backup restoration technique. To perform a full backup, first, uninstall the backup application followed by re-installation. Check that the account under which SQL service binds is the member of “Domain User Group” and has been provided with the Write access to the Windows server.
Verify if backup restoration is complete
The possibility is that the backup restoration is not complete. Although the above steps may help you restore some backup sets from the damaged device, it is mandatory to verify the integrity of the restored database. SQL server has the provision of saving the log details.
Details of Server logs help you in identifying the success or failure of backup operation or restore operation in the SQL Server error log as well as from the backup history tables in the msdb system database.
The Best Solution
What if manual restoration results in further information loss?
Sometimes SQL Database error 3013 still stands unresolved even after performing all the manual steps. You do not have any other option but to restore the data from a corrupt .bak file. In this case, you should try Stellar Toolkit for MS SQL to fix this error.
Why Stellar Toolkit for MS SQL?
- The software is trusted by Microsoft MVP
- This is the combination of three tools
When your backup system is hit with SQL Database error 3013, then you can either resort to manual methods to resolve the error or select Stellar Toolkit for MS SQL. Manual methods consume your time, give you jitters and leave you in the lurch when the problem stands unresolved. To avoid such situations you can do away with the manual method and install this toolkit.