By Delia / Last Updated December 20, 2021

Why SQL Server backup failed?

Why did the SQL Server backup fail when you followed the normal steps, and how to fix it? In fact, there are many possible reasons for database backup error, such as storage media failure, misoperation, incompatibility after software update, etc.

In this article, I will analyze some common cases and provide corresponding solutions. If you are tired of trying them one by one, or unfortunately none of them work well, you can also turn to the professional software for less failure and easier operation.

sql backup failed

Case 1. Operation system error 5 (Access is denied.)

When your SQL Server backup fails with the error "Operating system error 5 (Access is denied.)", this usually means you are not authorized to write data to the destination path. Then you can troubleshoot the following causes one by one:

  • Your login account of MSSQL does not has full permissions to the target directory.
  • There’s already a backup file of the same name on the target directory, and it’s read-only.

Fix 1 - Give full control to your SQL login account

To fix this error, first you can find your target folder, right-click on it and select Properties > Security to see the Group or usernames.

If the account you log SQL Server on is not listed here, or it only has limited permissions for this folder, this should be the reason for your SQL backup failure. And to fix it, you should provide full control (or at least write permission) to this account.

1. Press Win + R, type “services.msc” in the input box and click OK to invoke the Service window. Find the SQL Server service for the corresponding instance, then you can see its login account under Log On As column (or directly copy it from Properties).

sql log on account

2. If this account has been included in Group or usernames of the target folder, you just need to Edit it and give it Full control.

give full control

Otherwise, you may need to add this account manually. Click Edit > Add > copy the account name in the input box and click OK > select the matching name to add > give it Full control.

add account

Fix 2 - Cancel the read-only attribute of the backup file

If your login account already has full control over the target folder, or the above steps did not solve your problem, then you may also consider this possibility:

You’ve already created a backup file with the same name in the same destination path, and it has read-only property, so your newly created backup cannot overwrite it.

If this is the case, you just need to right-click the backup file, uncheck its Read-only attribute in Properties and Apply the change.

read-only backup file

For more details, you could refer to SQL Server backup to network share access denied.

Case 2. SQL Server error 3023 shrink failed for LogFile

When you perform a backup or shrink operation in SQL Server, you may receive the following error message:

Shrink failed for LogFile…
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft SQL Server, Error: 3023)

As stated in the prompt, the problem is usually caused by the conflict between backup, file manipulation operations and encryption changes.

For instance, you may encounter such errors (like 3023, 3013, 3041) when you try to make a full backup while doing differential backups, log backups, adding or dropping files to the database, shrinking files, etc.

Fix - Find out the conflict and retry the operation

To solve this problem, a common solution is to check the processes in progress, and retry the backup after stopping or finishing the conflicting operation.

Also, you can examine the schedules of all maintenance jobs in SQL Server, making sure they do not conflict with each other.

To check the status of each database, here is a query may help:

select name,
state_desc
from sys.databases

After executing it, the results will show you whether the database is ONLINE or RESTORING. If it’s stuck in the RESTORING status, you could fix it referring to SQL Server restore failed database in use.

check database status

In addition, SQL Server records the start and end time of each database backup. So you can also use the following query to check the backup history and determine if a full database backup is being performed when you attempt to execute an incremental backup.

select database_name, type, backup_start_date, backup_finish_date
from msdb.dbo.backupset
order by database_name, type, backup_start_date, backup_finish_date
Go

*In the results, D is for database backup, I for incremental backup, L for log backup and F for file backup.

check backup status

Case 3. SQL Server error 14234 the specified '@subsystem' is invalid

This error usually occurs when you create a maintenance plan, and it’s most likely because the SQL Integration Services hasn’t been installed.

Fix - Install SQL Server Integration Services (SSIS)

To install SSIS, first search for “SQL Server Configuration Manager” in Windows and launch it.

In the popping out window, click on SQL Service Services, then you can find SQL Server Integration Services on the right. Right-click it and Start it.

start sql server integration services

Backup SQL database with less error and simpler operation

What I listed above are only some common cases of SQL Server backup failure, in practice, there are more possible errors, especially when it comes to T-SQL scripts. So is there a simpler and less error-prone backup method? You can try AOMEI Centralized Backupper Database.

This centralized data management tool provides SQL Server Backup feature for all PCs and servers within LAN. It allows you to intuitively create backup tasks and directly restore databases from the created tasks.

Moreover, its graphic interface enables you to select multiple databases at once, and set a schedule to execute the backup task daily/weekly/monthly. It greatly simplifies the operation and reduces the possible errors during the progress.

Download the 30-day free trial to have a try:

Download Free Trial Win PCs and Servers
Secure Download

Install Centralized Backupper Database and Agent package on the central machine and the clients respectively, then you can control all the computers you want to manage, and continue to create a task.

request control

1. Navigate to Tasks > New Task > SQL Server Backup.

sql server backup

2. Click + Add Computers to detect and select the controlled computer with SQL instance.

add computers

3. Move to Step 2, and hit + Add to select instance and database for backup. You can select as many databases as you want.

add databases

4. Click on Step 3, and hit Add Storage to specify the target path of network share or NAS. The added path will be saved for your direct use.

add storage

5. You can add a Schedule to auto backup SQL databsase, or click Settings to encrypt or compress the backup. After the setup, click Start Backup > Create and execute tasks.

start backup

The created backup task will show in the Tasks tab, and you can directly restore databases from it.

restore database from backup

Supplement: Tips to protect your SQL database better

To maintain database security and avoid backup failures, it is also important to consider some uncontrollable factors in addition to being careful with the operation.

☛ Preventing media failure

  • Pay attention to disk maintenance and reliability.
  • Follow the 3-2-1 backup rule. Ensure that backups use multiple storage media.

 Prevent software problems

  • Pay attention to application updates. Most updates do not affect backups, but there can be a potential impact.
  • Monitor security configurations. Updates to security settings and policies can affect the connectivity of the backup system, which can affect backups.

Conclusion

When SQL Server backup fails, you can try above methods to fix some common errors, or simply adopt a reliable program to protect databases with less error and easier operation.

AOMEI Centralized Backupper Database Edition is such a SQL backup software. It can centrally manage all computers and servers in your LAN, and simply automate SQL backups, backup all databases at once, restore multiple SQL databases, etc. Besides SQL database, it is also suitable for other computer data such as Windows OS.