Microsoft sql server error 3013



SQL Server Error 3013: Restore Database is Terminating Abnormally

Overview of SQL Server Error 3013

One of the most frustrating situations that a user experiences while working with SQL Server is when errors are encountered. These errors disrupt the normal functioning of SQL Server and in worst case scenario may even lead to data loss. Sometimes, SQL Server Backup Detected Corruption In the Database Log. In this write-up, we are going to discuss about one such SQL Error 3013.

Error Description

SQL Server Error 3013 mainly occurs when the backup process to a disk/tape or a restoration process from a disk/tape is under process.

What is «Restore Database Terminating Abnormally» in SQL Error 3013 ?

The error message and the occurrence factors of SQL Server error 3013 vary according to the SQL Server version the user is currently working with.

SQL Server 7.0

In this SQL version, the error is encountered when the clustered index has been created in all the filegroups of the table.

SQL Server 2000

In this case, if the database of size 2 GB already exists and a separate database of size more than 2 GB is restored in the existing database, the error is generated.

SQL Server 2005

In the instance of restoration of both the data and log files at the same time, the Error 3013 is encountered.

Reason behind Its Occurrence

The dominant reason behind the occurrence of this error is the inability of the database to read the filemark present in the backup device or inaccessibility of the filemark itself. Before proceeding further, let us know what filemark actually is.

A filemark in a backup device is the element that does not store user data. In order to organize and unify the storage patterns, the filemarks, divide the partition in smaller segments. Their primary task is splitting data of all the individual files that are stored in the backup device.

The other reasons, which also play an important role in the occurrence of SQL error 3013, are:

  • Media failure on the device in which the backup has been stored.
  • Write failure whilst the execution of the backup process.
  • Connectivity loss during the network backup process.

Workaround

1. The error can be removed by manual deletion of the device and ensuring that the server performs new backup to the device. The command to do so is:

2. In case the restoration process is the reason behind the error occurrence, it is possible to retrieve the other backup data by specifying the file number in the device.

In order to check the presence of multiple backup sets on the device, run the following code:

For indicating a specific set of backup, run the following command:

Note: The FileNumber term is used to represent the file series that the user wants to restore

Conclusion

In the blog, we discussed about the SQL Server Error 3013. With the help of the workarounds mentioned in the above section, the users can easily remove this error and continue with the restoration and backup process. In case if you failed with manual solution then you can also try SQL BAK recovery tool to successfully repair corrupt SQL .bak file.

Источник

Error 3266 or 3013 when you perform a database backup to disk or tape or a database restore from disk or tape

This article provides helps to solve error 3266 or 3013 that occurs when you perform a database backup to disk or tape or a database restore from disk or tape.

Applies to: В Windows Server 2012 R2
Original KB number: В 290787

Symptoms

When you perform a database backup to disk or tape, or a restore from disk or tape, the following error message may occur:

SQL Server 7.0 Server:

Msg 3266, Level 16, State 1, Line 1
The Microsoft Tape Format (MTF) soft filemark database on backup device ‘devicename’ cannot be read, inhibiting random access.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

SQL Server 2000 Server:

Msg 3266, Level 16, State 1, Line 1
The backup data in ‘devicename’ is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

SQL Server 2005 Server:

Msg 3013, Level 16, State 1, Line 1
The backup data at the end of ‘devicename’ 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.
Server: Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Читайте также:  Error connection api client

Cause

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:

A media failure may occur on the device where the backup is located.

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.

Workaround

To allow SQL Server to perform new backups to the backup device, you must manually delete or erase the device by using the following command:

If the error message occurs during a restore operation, it may be possible to retrieve other backup sets from the device by specifying the file number. For example, if three (3) backups were on one (1) backup device, backup sets 1 and 2 may be usable. To determine if multiple backup sets are on a device, run the following code from Query Analyzer:

Each backup set has one entry in the output. To indicate a specific backup set, use this code:

FileNumber is the backup set number you want to restore.

More information

The following list contains important notes regarding backups and SQL Server.

After SQL Server detects a filemark error on a device, SQL Server does not write additional information to the device.

SQL Server stores all backups in the Microsoft Tape Format, whether the backup is made to disk or to tape. The Microsoft Tape Format uses filemarks to hold information such as the block size and the number of blocks in a backup, in addition to other information about the backup. The Microsoft Tape Format also uses filemarks to delimit backups in a backup device. The fact that a filemark is missing or is damaged, suggests that at least one backup on the device is not valid.

Although you may be able to restore some backup sets from the damaged device, you must verify the integrity of the restored database.

SQL Server logs details of success or of failure during a backup operation or a restore operation in the SQL Server error log and in the backup history tables in the msdb system database.

If you experience an error 3266 when you restore a transaction log or a database backup, investigate the following logs for more information:

  • SQL Server error log
  • Backup and restore history tables
  • Application event log
  • System event log

If there are no details of the failure in these logs, you may have experienced an unreported failure. You should contact Microsoft Product Support Services if you need help.

Источник

Error 3266 or 3013 when you perform a database backup to disk or tape or a database restore from disk or tape

This article provides helps to solve error 3266 or 3013 that occurs when you perform a database backup to disk or tape or a database restore from disk or tape.

Applies to: В Windows Server 2012 R2
Original KB number: В 290787

Symptoms

When you perform a database backup to disk or tape, or a restore from disk or tape, the following error message may occur:

SQL Server 7.0 Server:

Msg 3266, Level 16, State 1, Line 1
The Microsoft Tape Format (MTF) soft filemark database on backup device ‘devicename’ cannot be read, inhibiting random access.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

SQL Server 2000 Server:

Msg 3266, Level 16, State 1, Line 1
The backup data in ‘devicename’ is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

SQL Server 2005 Server:

Msg 3013, Level 16, State 1, Line 1
The backup data at the end of ‘devicename’ 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.
Server: Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Cause

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:

Читайте также:  Error caused by file delete

A media failure may occur on the device where the backup is located.

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.

Workaround

To allow SQL Server to perform new backups to the backup device, you must manually delete or erase the device by using the following command:

If the error message occurs during a restore operation, it may be possible to retrieve other backup sets from the device by specifying the file number. For example, if three (3) backups were on one (1) backup device, backup sets 1 and 2 may be usable. To determine if multiple backup sets are on a device, run the following code from Query Analyzer:

Each backup set has one entry in the output. To indicate a specific backup set, use this code:

FileNumber is the backup set number you want to restore.

More information

The following list contains important notes regarding backups and SQL Server.

After SQL Server detects a filemark error on a device, SQL Server does not write additional information to the device.

SQL Server stores all backups in the Microsoft Tape Format, whether the backup is made to disk or to tape. The Microsoft Tape Format uses filemarks to hold information such as the block size and the number of blocks in a backup, in addition to other information about the backup. The Microsoft Tape Format also uses filemarks to delimit backups in a backup device. The fact that a filemark is missing or is damaged, suggests that at least one backup on the device is not valid.

Although you may be able to restore some backup sets from the damaged device, you must verify the integrity of the restored database.

SQL Server logs details of success or of failure during a backup operation or a restore operation in the SQL Server error log and in the backup history tables in the msdb system database.

If you experience an error 3266 when you restore a transaction log or a database backup, investigate the following logs for more information:

  • SQL Server error log
  • Backup and restore history tables
  • Application event log
  • System event log

If there are no details of the failure in these logs, you may have experienced an unreported failure. You should contact Microsoft Product Support Services if you need help.

Источник

SQL Server Error 3013 – BACKUP DATABASE is terminating abnormally

Summary: This blog will discuss version-specific occurrences of the SQL Server Error 3013, the reasons behind the error, and methods to fix it. If you cannot restore the database (DB) from backup, it usually means that the backup (.bak) file is damaged or corrupt. There is no manual method to restore the database from a corrupt .bak file. In that case, use Stellar Repair for MS SQL Technician for backup recovery to recover the DB from the backup (.bak) file.

Sometimes, when performing an SQL Server database (DB) backup to a storage device or trying to restore the DB from backup, you may encounter the following error message:

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

The frequency of this error may vary depending on the versions of SQL Server application you are using.

Version-Specific Occurrences of SQL Error 3013

  • SQL Server 7.0: In this SQL version, the error occurs when a clustered index is created in every filegroup of the table. The error message is as follows:

  • SQL Server 2000: If the database of volume less than 2 GB is already available and an attempt is made to back up another database having more than the existing volume, it results in 3013 error code. And, you will receive an error message similar to:

  • SQL Server 2005: In this version, the error occurs when Backup Administrator tries to restore data files and log files together in a single instance of time. The error message appears as:

What Causes SQL Server Error 3013?

Plausible reasons that result in SQL Server restore database is terminating abnormally error are as follows:

  • An attempt to view the Network drive has been made by an unauthorized user.
  • The storage device, on which the backup file (.bak) is stored, has failed.
  • A write failure has occurred during backup creation.
  • When there is not enough storage on the backup drive.
  • When an attempt is made to execute backup on transactional logs when Database is in SUSPECT mode.
Читайте также:  Load failed plugin file open error plugin

Methods to Fix SQL Server Error 3013

NOTE: Since backup has terminated abruptly, avoid rewriting the same backup, as it may result in the same error again.

Depending on the version-specific occurrence of SQL Server Error 3013, follow these methods to fix the error:

NOTE: Methods 1, 2, and 3 may resolve the problem, provided the error has not occurred during backup restoration. But, if you have encountered backup failed error 3013 during the restoration process, skip to Method 4.

Method 1 – Check the Security Permission for User

Follow these steps to check if a user is denied permission to take DB backups in SQL Server:

Step 1: Browse the location of the backup folder to find the database ‘.bak’ file.

Step 2: Right-click the backup file, and select Properties.

Step 3: In the Properties window, click the Security Tab.

Step 4: Now check the Deny permissions for Authenticated Users.

Step 5: Click Edit and remove the denied permission.

Step 6: Click OK.

Method 2 – Delete the Previous Backup

Manually delete the previous backup and enable the SQL server to execute new backups to the backup device to fix the error. Use the following command for manual deletion of the last backup:

Method 3 – Perform Full Backup Restoration

Sometimes, partial restoration is not the solution, try performing a 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 ‘Write’ access to the Windows server.

Method 4 – Try Retrieving another Backup Set

If backup restoration is behind the error, try retrieving other backup sets within the backup device by specifying the file number.

NOTE: The file number signifies the backup set series that needs to be restored.

Run the following command to retrieve the backup set from Query Analyzer:

Next, specify the particular backup set for retrieval by using the following command:

The above command may help you restore some backup sets from the damaged device, but there is a possibility that the backup restoration process is not complete. So, it is mandatory to verify the integrity of the restored database.

You can identify 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.

What if You Cannot Restore SQL Server Database from Backup?

If you’re unable to restore your database from the .bak file, it is likely that the file has turned corrupt. And, you cannot restore from a corrupt .bak file. But, you can try to extract data from the file by using Stellar Repair for MSSQL Technician software. It is trusted by Microsoft MVPs and is a combination of 3 powerful tools that help repair corrupt SQL Server database, extracts database from corrupt .bak file, and reset SQL Server password.

Conclusion

When your backup system is hit with SQL Server Error 3013 before restoring the database, you can try any of these manual methods to fix the error:

  • Check for user security permission and change it.
  • Try deleting the old data from the backup device and enable SQL Server application to implement a new backup operation.
  • Try performing a full backup restoration.

If you get a 3013 error when restoring the DB, check for other backup sets available on the storage device and retrieve them. But, if you’re unable to restore the .bak file, chances are that the file is corrupt. If that’s the case, you can try extracting data from the .bak file by using the Stellar Repair for MS SQL Technician software.

About The Author

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software’s. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

Best Selling Products

Stellar Data Recovery Professional for Windows

Stellar Data Recovery has the right Windows Recovery tool for all your data recovery

Stellar Data Recovery Professional for Mac

Stellar Data Recovery for Mac program performs safe..

Stellar Photo Recovery

A comprehensive photo recovery software to restore photos, music & video files

Stellar Repair for Video

Powerful video repair tool for repairing corrupt or damaged MOV and other video files

Источник

Оцените статью
toolgir.ru
Adblock
detector