Sqlstate 42000 ошибка 3013

Fix SQL Server Backup Error – Backup Database is Terminating Abnormally. sqlstate 42000 (Error 3013)

Let us assume that you are restoring a database backup from any disk or tape. While doing this, you have changed the logical name of the database. And then you may receive the following error message: SQL Server backup error 3013 or Msg 3013, Level 16, State 1, Line 1 Backup database is terminating abnormally

This error is interconnected to restoring the database. Using the statement RESTORE FILELISTONLY, a user can check the backup file. You will notice that the modified logical name of the database is corrupted or damaged and the last character of the name is truncated.

Consider the following user Query: SQL Server Backup Error 3013

Clark Williams: “Hi. The problem is when we tried to backup and restore our database using SQL Server 2008, we got an error. The schedule was to create seven backups of twenty-three databases. The schedule worked fine for some time. But, from the past two days, all the backups failed. The server is showing the following error:

Write on “T:\.bak” failed: 170(The requested resource is in use.)
Msg 3013, Level 16, State 1, Server, Line 1
BACKUP DATABASE is terminating abnormally.
Outcome: Failed

What would cause the above problem to occur? How should I correct it and prevent it from happening again?”

Important Note: In case if your SQL Server Backup files are corrupted and you are looking for a Quick Solution to repair corrupted .bak files then I suggest you take the help of SQL Backup Recovery Software. For complete details, the user can try the demo version of this software.

Download Now Purchase Now 100% Secure

Causes For the SQL Server Backup Database Error 3013

Oftentimes, when an administrator is backing up the database, SQL 3013 flashes up on the screen. The possible reasons due to which this SQL server backup error 3013 is displayed are:

• Write failure occurred while creating the backup: When there is insufficient storage space on the backup drive, the file gets compressed.
• When unauthorized users attempt to view network drive, SQL Server may result in the error.
• Media failure occurred: When the storage device where the backup file is saved undergoes any media failure, this may also result in SQL Server Error
• When the database is in Suspect mode, and the user tries to perform a backup of the transactional log, the error may occur.

Dependency upon Versions of SQL Server and Operating Systems

Errors in the SQL Server are dependent on the operating system and the versions of SQL application. The stated point will become more clear in the following section:

• SQL Server 7.0: In this version, an error occurs when a clustered index is created corresponding to each filegroup.
• SQL Server 2000: This error occurs in relation to another database. There are two possible situations under which the error in this version occurs:
  First, the database is lesser than 2 GB in size
  Second, the other database is having size more than the size of the pre-existing database
• SQL Server 2005: In this version, the error occurs when an administrator tries to back up both data files as well as log files simultaneously.

Workaround to Fix MSG 3013, level 16, state 1, line 1 Backup Database is Terminating Abnormally

To restore SQL Error 3013 manually, there are 2 manual methods. Both of them are described in detail:

#Solution 1

In this method, you need to take the database offline after changing the logical name of the database and then again bring the database online.

Following command needs to be performed:

#Solution 2

When you change the logical name of the database, insert a white space at the end of new file name. The command to implement the following change is:

Note: It is possible that you have to restart the database after changing the logical name of your database

Limitations of the Manual Solution to Troubleshoot SQL Server 2012 Backup Error 3013

Although the manual solution is free of cost, there are some of the drawbacks associated with the manual approach. The limitations of the manual method to fix SQL Server Backup Error 3013 are listed below:

• The workarounds depends on the version of the SQL Server
• A user must have a good technical knowledge regarding SQL Server
• Restoration using manual method may result in loss of information.

Professional Solution to Fix Backup Database is Terminating Abnormally. Sqlstate 42000 (error 3013)

The best solution to resolve the error is to use a third-party tool like Revove SQL Database Repair Tool. It is the trouble-free solution to fix msg 3013, level 16, state 1, line 1 backup database is terminating abnormally. According to the experts, it is the master solution to fix SQL Server 3013 error. By using this the user can easily recover the SQL Server database and also the user can export the data to SQL Server easily. Some of the features which make it distinct from other available products are:

• A file can be saved with two options: With only schema or With Schema & Data
• Supports SQL Server 2019, 2017 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000 versions
• Preview and Recover Deleted SQL Table Records
• Supports SQL Server version 2019 and below versions.

Note: The user can also read the another similar post to fix SQL Server error 823.

Conclusion

When an admin change the logical name of the database while creating the backup of SQL file, SQL Server Backup error 3013 occurs. There are workarounds to eliminate the error. But, the problem is that the manual method is not that effective to remove the error. In fact, it may also result in loss of data. To overcome these problems, users can opt for an automated solution. This is the professional solution to fix SQL Server Backup Error 3013, level 16, state 1, line 1 backup database is terminating abnormally.

Hi,

I run 2 steps viz with NORECOVERY and with RECOVERY in sequence as SQP jobs. The 2nd step fails some times. When it starts failing, I run full backup of the production server again and the 2nd step starts succeding. Unless I run the full backup the 2nd step keeps on failing.

What are the conditions which generate  for [SQLSTATE 42000] (Error 3013) and how to tackle this problem.?

The 2 steps are :-

1.RESTORE DATABASE ARSYSTEM
FROM DISK = ‘E:SQL backup from productionsql_full_backup’
WITH
Replace ,
MOVE ‘arsystem’ TO ‘d:ardataarsystem.mdf’ ,
MOVE ‘arsystem_log’ TO ‘D:ARLOGARsystem’ ,
NORECOVERY
GO

2.RESTORE DATABASE ARSYSTEM
FROM DISK = ‘E:SQL backup from productionSQL daily diff back up’
WITH
Recovery

The status of the 2 steps is :

1 Executed as user: BPOTESTREMEDYremedybackup. Processed 104480 pages for database ‘ARSYSTEM’, file ‘ARSystem’ on file 1. [SQLSTATE 01000] (Message 4035)  Processed 1 pages for database ‘ARSYSTEM’, file ‘ARSystem_log’ on file 1. [SQLSTATE 01000] (Message 4035)  RESTORE DATABASE successfully processed 104481 pages in 49.452 seconds (17.307 MB/sec). [SQLSTATE 01000] (Message 3014) 

 2. Cannot apply the backup on device ‘E:SQL backup from productionSQL daily diff back up’ to database ‘ARSYSTEM’. [SQLSTATE 42000] (Error 3136)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

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.

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:

BACKUP DATABASE mydatabase TO DISK= ‘C:Mydatabase.bak’ with format

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:

RESTORE HEADERONLY FROM DISK=’C: MyDatabase.bak

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

RESTORE DATABASE mydatabase FROM DISK=’C: MyDatabase.bak WITH FILE = FileNumber

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.

Fix SQL Server Backup Error – Backup Database is Terminating Abnormally. sqlstate 42000 (Error 3013)

admin ~
Modified: 13-04-2020 ~ SQL Server , Technology ~ 5 Minutes Reading

Let us assume that you are restoring a database backup from any disk or tape. While doing this, you have changed the logical name of the database. And then you may receive the following error message: SQL Server backup error 3013 or Msg 3013, Level 16, State 1, Line 1 Backup database is terminating abnormally

This error is interconnected to restoring the database. Using the statement RESTORE FILELISTONLY, a user can check the backup file. You will notice that the modified logical name of the database is corrupted or damaged and the last character of the name is truncated.

Consider the following user Query: SQL Server Backup Error 3013

Clark Williams: “Hi. The problem is when we tried to backup and restore our database using SQL Server 2008, we got an error. The schedule was to create seven backups of twenty-three databases. The schedule worked fine for some time. But, from the past two days, all the backups failed. The server is showing the following error:

Write on “T:.bak” failed: 170(The requested resource is in use.)
Msg 3013, Level 16, State 1, Server, Line 1
BACKUP DATABASE is terminating abnormally.
Outcome: Failed

What would cause the above problem to occur? How should I correct it and prevent it from happening again?”

Important Note: In case if your SQL Server Backup files are corrupted and you are looking for a Quick Solution to repair corrupted .bak files then I suggest you take the help of SQL Backup Recovery Software. For complete details, the user can try the demo version of this software.

Download Now Purchase Now 100% Secure

Causes For the SQL Server Backup Database Error 3013

Oftentimes, when an administrator is backing up the database, SQL 3013 flashes up on the screen. The possible reasons due to which this SQL server backup error 3013 is displayed are:

• Write failure occurred while creating the backup: When there is insufficient storage space on the backup drive, the file gets compressed.
• When unauthorized users attempt to view network drive, SQL Server may result in the error.
• Media failure occurred: When the storage device where the backup file is saved undergoes any media failure, this may also result in SQL Server Error
• When the database is in Suspect mode, and the user tries to perform a backup of the transactional log, the error may occur.

Dependency upon Versions of SQL Server and Operating Systems

Errors in the SQL Server are dependent on the operating system and the versions of SQL application. The stated point will become more clear in the following section:

• SQL Server 7.0: In this version, an error occurs when a clustered index is created corresponding to each filegroup.
• SQL Server 2000: This error occurs in relation to another database. There are two possible situations under which the error in this version occurs:
  First, the database is lesser than 2 GB in size
  Second, the other database is having size more than the size of the pre-existing database
• SQL Server 2005: In this version, the error occurs when an administrator tries to back up both data files as well as log files simultaneously.

Workaround to Fix MSG 3013, level 16, state 1, line 1 Backup Database is Terminating Abnormally

To restore SQL Error 3013 manually, there are 2 manual methods. Both of them are described in detail:

#Solution 1

In this method, you need to take the database offline after changing the logical name of the database and then again bring the database online.

Following command needs to be performed:

#Solution 2

When you change the logical name of the database, insert a white space at the end of new file name. The command to implement the following change is:

Note: It is possible that you have to restart the database after changing the logical name of your database

Limitations of the Manual Solution to Troubleshoot SQL Server 2012 Backup Error 3013

Although the manual solution is free of cost, there are some of the drawbacks associated with the manual approach. The limitations of the manual method to fix SQL Server Backup Error 3013 are listed below:

• The workarounds depends on the version of the SQL Server
• A user must have a good technical knowledge regarding SQL Server
• Restoration using manual method may result in loss of information.

Professional Solution to Fix Backup Database is Terminating Abnormally. Sqlstate 42000 (error 3013)

The best solution to resolve the error is to use a third-party tool like Revove SQL Database Repair Tool. It is the trouble-free solution to fix msg 3013, level 16, state 1, line 1 backup database is terminating abnormally. According to the experts, it is the master solution to fix SQL Server 3013 error. By using this the user can easily recover the SQL Server database and also the user can export the data to SQL Server easily. Some of the features which make it distinct from other available products are:

• A file can be saved with two options: With only schema or With Schema & Data
• Supports SQL Server 2019, 2017 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000 versions
• Preview and Recover Deleted SQL Table Records
• Supports SQL Server version 2019 and below versions.

Note: The user can also read the another similar post to fix SQL Server error 823.

Conclusion

When an admin change the logical name of the database while creating the backup of SQL file, SQL Server Backup error 3013 occurs. There are workarounds to eliminate the error. But, the problem is that the manual method is not that effective to remove the error. In fact, it may also result in loss of data. To overcome these problems, users can opt for an automated solution. This is the professional solution to fix SQL Server Backup Error 3013, level 16, state 1, line 1 backup database is terminating abnormally.

SQL Server Database Administration

Different kinds of database exits each adhering to a different model of managing or organizing data in the database. Here is a blog to discuss the on going issues and try to find a resolution.

Subscribe To

Thursday, May 17, 2018

SQL Server Job Failed [SQLSTATE 42000] (Error 3013) and (Error 3202)

[SQLSTATE 01000] (Message 3211) Write on
«backup-locationfolderdatabase_name.BAK»
failed: 2(The system cannot find the file specified.) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

The sql jobs are failing due to network dis-connectivity to the network location where my backups will be written to.
So I made use to retry_attempts and retry_interval parameter options available in job properties. as show below pic.

But I have 102 steps, I don’t want to waste my time in updating all the job steps, instead I want to update all steps at once. Here is the query that I used to update all steps of a particular job that I wanted to update retry_interval and retry_attempts of a job.

TO SET RETRY ATTEMPTS to the job
Update sysjobsteps
Set retry_attempts=3 (—set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name=’JOB NAME’

TO SET RETRY INTERVALS to the job

Update sysjobsteps
Set retry_interval=1 (—set as you prefer)
from sysjobs S
inner join [dbo].[sysjobsteps] j
on j.job_id=S.job_id
where name=’JOB NAME’

Note: Run at your own risk. It worked for me. This may be not the ideal solution since it is more related to the Network packets being dropped, and in-stable Network. I spent several months on resolution for network issues. But I used this, hope this helps others.

It is very good blog and useful for students and developer ,

Sql server DBA Online Training Bangalore

Deep Learning Projects assist final year students with improving your applied Deep Learning skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include Deep Learning projects for final year into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Deep Learning Projects for Final Year even arrange a more significant compensation.

Thanks for clarifying such details! Faced above error for two month with 110 gb db when backup to network share the throws errors with status = 1450 (1450 insufficient system resources), then sqlstate 4200 error 3202 and error 3013. Agent job for backup db to network location some times executed normal, sometimes throw these errors instantly. Read alot of articles related to os regkeys pagedpoolsize, poolusagemaximum, irpstacksize it doesn’t seem to workaround here and that error 1450 is total misleading to resolve the problem. I thought about time crossing of the scheduled jobs at evenings and nights at first. Changed times of this error job with no luck. And then thought about retry attempts and such errors goes away when i set retry attempts and intervals. Then again set them off and restart the server errors goes back. Main detail that above errors 3202 and 3013 coul appear at different day intervals. I tried evenings, mornings,nights. So thanks again!

Источник

Sqlstate 42000 error 3202

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I have been backing up the same 10 databases with differential backups on the daily biases for about a month and last night, one of the databases failed to back up with the following error messages:

112(error not found) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Would someone be able to let me know why this would happen just suddenly?

Answers

• Proposed as answer by Balmukund Microsoft employee Sunday, August 22, 2010 5:33 PM
• Marked as answer by Vijay Sirohi Editor Monday, September 20, 2010 3:37 AM

check whether any other process are running while backup job is running. I had same type of situation and found that one of tape backup (backup exec) was using high cpu and backup jobs were failing. May be some monitoring process (third party) were utilizing high CPU.

Restarting other service(not sql service) will resolve your issue. If no one is utilizing high cpu other than SQL server, run sql profile to findout high cpu utilizing process.

Источник

Sqlstate 42000 error 3202

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

We are running SQL Server 2016 Std edition in Azure, and have configured Managed Backup (previously SmartAdmin from 2014).

When running the backup on demand stored procedure, one of our databases fails with the following message:

We are patched up to SQL Server SP1 + CU1.

Answers

Communication on this issue has been quite poor but this appears to have been resolved by SQL 2016 SP1 + CU2.

I can only confirm once CU2 makes it into production, but similar issues in other environments no longer appear.

Have you tried » managed_backup.sp_get_backup_diagnostics » to get more info if there’s any?

Is this a consistent problem that occurs everytime with the same database? What’s the size of the database you are having the problem with? Is there any specific difference of this specific database from the others for which the operation is completed successfully?

The output of that isn’t very easy to understand, but the following row from that would be relevant.

I can confirm that there is no corresponding backup in Azure storage container.

I can also confirm that doing the exact same for other smaller databases works just fine, and that the database that is failing does successfully backup to local storage.

Obviously the error message is a bit generic. With that I had the same error message when I was trying to create my on-prem database’s database files on Azure Blob Storage because I didn’t have a proper Credential on-prem SQL Server instance for the related Azure Blob Container.

I know you said that the same code works with some other databases, but I just wanted to share my experience with you, you know just in case so that it might give you an idea about the possibility of the problem.

Thanks for trying to help.

Just tried to do a BACKUP DATABASE TO URL and received a similar error:

We are using SAS tokens at the moment. I’ll setup a credential with Access Keys and try it all again. I’ll post my findings here.

It should not be about the Credential, because then the backup wouldn’t start at all. But according to your last post, the process had begun but then interrupted.

If the database would be on-prem and you were trying to backup to Azure Storage, then my suspect at this point would be a possible inconsistent network connection. You in your first post you said the VM and the database on it is on Azure.

In all cases the backup appears start, and I can observe both outbound network traffic and the initial blob entries in the storage account (with their leases) at 0 byte sizes. Presumably they are writing into those blobs.

The same behavior can be seen for smaller databases.

But after some time yes, the above stated error happens to the one large database.

The backup is being made from an Azure VM to an Azure Storage Account in the same data-center, so no reason to expect network connectivity issues.

I was able to create a local database backup and use AzCopy to manually upload that backup as a blob to the same account — without any issue.

Interestingly I’m doing a BACKUP DATABASE TO URL now using an Access Key based credential (instead of the SAS token one) and it’s gone much further than before and is already sitting at 20% completed.

Equally interesting, the storage account shows the blob for the backup now as a PAGE blob (instead of the more inexpensive BLOCK blob). Not sure how I can control / tell BACKUP DATABASE TO URL to create a BLOCK blob instead.

«Blob: A file of any type and size. There are two types of blobs that can be stored in the Microsoft Azure Blob storage service: block and page blobs. SQL Server backup can use either blob type depending upon the Transact-SQL syntax used.»

However I could not find the details about the Transact-SQL syntax that could be used to declare the blob type from the documentation.

I tried one of the latest versions of SSMS to generate a BACKUP TO URL script, but I don’t see a related syntax about blob types.

Thanks for that.

It would seem that with SQL 2016, when you issue a BACKUP DATABASE TO URL statement, if you include a WITH CREDENTIAL» then that credential needs to be an access token credential. In that scenario it will always create a PAGE blob (something about legacy).

However, if you have already created a SAS token, that token includes the both the Azure Storage Account and Container information. As a result you can issue a BACKUP DATABASE TO URL without the «WITH CREDENTIAL» clause and it will then lookup the corresponding SAS token to match the URL. These backups are always BLOB based backups.

As for my issue, I have cleared out all SQL Managed Backup configurations, removed all credentials and started fresh with a newly created SAS token for the container and setup SQL accordingly.

My first test was to just do a BACKUP DATABASE TO URL using the new SAS token and that failed in the same way as before.

I also tried using the «On_demand» backup from SQL Managed Backup functionality and I still have the same issue.

Looks like SQL Managed Backup cannot cope with moderately sized databases. Small ones — yeah fine, but 600GB not so much.

Thanks for sharing!

I think you might want to file a bug in the Microsoft Connect about this problem. Just ensure that the problem is reproducable before reporting this problem as a bug.

Because I have not seen anywhere for a possible default timeout limitation for this kind of operation and it just doesn’t make sense. There must be something wrong somewhere. I’m already out of Azure credit for January, otherwise I’d give it a try.

Thank you for trying to help.

Thank you for trying to help.

I had the same issue and my friendly sysadmin reminded me that block blobs have a file size limit of 200GB.

Try striping the backups: https://www.datavail.com/blog/striped-sql-server-backups-with-azure-blob-storage/

It doesn’t look like so, check out the following documentation:

» Block blobs let you upload large blobs efficiently. Block blobs are comprised of blocks, each of which is identified by a block ID. You create or modify a block blob by writing a set of blocks and committing them by their block IDs. Each block can be a different size, up to a maximum of 100 MB (4 MB for requests using REST versions before 2016-05-31), and a block blob can include up to 50,000 blocks. The maximum size of a block blob is therefore slightly more than 4.75 TB (100 MB X 50,000 blocks). For REST versions before 2016-05-31, the maximum size of a block blob is a little more than 195 GB (4 MB X 50,000 blocks). » (Comment: I think this is that I made italic is what your sysadmin mentioned, but it seems outdated)

» The maximum size for a page blob is 1 TB. «

So, I’ve managed to catch the core of the issue with SQL 2016 Managed Backups to Azure.

The following SQL was profiled during the failed backup:

The above command is automatically issued by SQL Server 2016 Managed Backup based on it’s configuration, etc.

The @file_count parameter is the root of the issue and I suspect the «logic» behind the Smart Admin / Managed Backup that determines the @file_count for the striped backup is flawed.

The value of 3 above is woefully too small given the size of the database, and in this particular database’s case, I needed to have the @file_count = 9 for the above statement to succeed. (The 9 database files average around 30-40GB each).

No if only someone from the SQL Server team can respond and acknowledge the this or the connect issue. I’ve even Tweeted to the SQLServer team — but not getting any joy.

We’re root causing this issue, but I suspect that your reasoning is correct.

SQL Server, Microsoft

Thank you for the confirmation Luis

Are you able to offer any possible indication on timelines for fix/workaround?

This is getting a bit long in the tooth for us? Is managed backup being canned?

(I’m not going away)

Communication on this issue has been quite poor but this appears to have been resolved by SQL 2016 SP1 + CU2.

I can only confirm once CU2 makes it into production, but similar issues in other environments no longer appear.

I’m using SQL 2016 (SP1-CU2)

I backed up using the following and it works. This one backed with compression to approx. 45 GB.

backup database Database1
to url = ‘https://someblobstorage.blob.core.windows.net/backup/Database1.bak’
WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 5;

However, if I backup and take the Compression out it fails. This one backed up to 171 GB and then failed.

Which users are saying that there is a 200 GB limit. My DB is 300 GB uncompressed.

backup database Database1
to url = ‘https://someblobstorage.blob.core.windows.net/backup/Database1.bak’
WITH MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 5;

SQL Logs show the following

Write to backup block blob device https://someblobstorage.blob.core.windows.net/backup/Database1.bak failed. Device has reached its limit of allowed blocks.

Also it shows the following when it finally fails.

BackupIoRequest::ReportIoError: write failure on backup device ‘https://someblobstorage.blob.core.windows.net/backup/Database1.bak’. Operating system error 1117(The request could not be performed because of an I/O device error.).

Here is a more detailed message on the job log error.

[SQLSTATE 01000] (Message 3211) 10 percent processed.
[SQLSTATE 01000] (Message 3211) 15 percent processed.
[SQLSTATE 01000] (Message 3211) 20 percent processed.
[SQLSTATE 01000] (Message 3211) 25 percent processed.
[SQLSTATE 01000] (Message 3211) 30 percent processed.
[SQLSTATE 01000] (Message 3211) 35 percent processed.
[SQLSTATE 01000] (Message 3211) 40 percent processed.
[SQLSTATE 01000] (Message 3211) 45 percent processed.
[SQLSTATE 01000] (Message 3211) 50 percent processed.
[SQLSTATE 01000] (Message 3211) 55 percent processed.
[SQLSTATE 01000] (Message 3211) Write on «https://someblobstorage.blob.core.windows.net/backup/Database1.bak»
failed: 1117(The request could not be performed because of an I/O device error.)
[SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

The BLOB properties for the Backup file in Azure is the following below.

Источник

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.

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:

BACKUP DATABASE mydatabase TO DISK= ‘C:Mydatabase.bak’ with format

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:

RESTORE HEADERONLY FROM DISK=’C: MyDatabase.bak

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

RESTORE DATABASE mydatabase FROM DISK=’C: MyDatabase.bak WITH FILE = FileNumber

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.

I have a backup job that takes a full backup of my production database on a daily basis.

It was running fine but all of a sudden, it failed with the following error message.

Message

Executed as user: NT AUTHORITYSYSTEM. Write on «D:MSSQLSQLBackupProd_CT.bak» failed: 1(error not found) [SQLSTATE 42000] (Error 3202) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

I tried executing the backup statement from the Query analyzer and that also failed with the same message.

I changed the location of the backup file to D:Prod_CT.bak and tried executing from the query analyzer and it worked fine.

The syntax used for the backup job is as mentioned below.

backup database Ctrack to disk = ‘D:MSSQLSQLBackupProd_CT.bak’ with init

Any pointers would be of much help.