SQL Server Backup Failed | Solutions and Alternative

This article introduces several common causes of SQL Server database backup failure and provides corresponding solutions in different cases.

Delia

By Delia / Updated on January 29, 2024

Share this: instagram reddit

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 bonus Bonus part in this post to use an alternative way to backup SQL server without error.

sql backup failed

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

When your SQL Server backup failed 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 SQL Server backup failed access denied, 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

If SQL Server backup failed when you create a maintenance plan, and prompt you a message like this, 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

 

Bonus: Alternative way to easily backup SQL databases without error 

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 Cyber Backup, a professional and stable SQL database backup solution.

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 TrialMicrosoft SQL Server 2005-2022
Centralized and secure SQL backup

 

👉How to set up the SQL Server backup software:

1. Run the installed AOMEI Cyber Backup on the server machine, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

Microsoft SQL

Choose Download proxy program (or Copy link) and install it on to the device with SQL Server installed. Then, click Already installed proxy and select the device.

Next, click icon -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Add Device

👉How to perform SQL Server database backup successfully:

1. Click Backup Task -> Create New Task to launch the task creating page. Choose backup type as Microsoft SQL Backup.

Backup Type

2. Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.

Select Database

3. Select Target to save backups. You can either specify a local path or network path. The added path will be saved for later use.

Choose Target

4. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly, and set the backup method as Full, Incremental or Differential Backup.

Execute the task. Once it's created, you will find it in the Backup Task tab.

✍More useful features:

  • Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
  • Email Notification enables you to receive email notifications when the task is abnormal or successful.

Supplement: Tips to protect your SQL database better

The above analyses some reasons for backup failure in SQL Server, but there are many other possibilities that may cause problem. 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

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

AOMEI Cyber Backup is such a SQL backup software. It can centrally manage all devices 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 virtual machine backup.

Delia
Delia · Editor
Delia owns extensive experience in writing technology-related blog posts, and has been a part of AOMEI since 2020 to provide expertise in data security and disaster recovery. She works with Windows operating systems, SQL databases, and virtualization platforms such as VMware and Hyper-V, specializing in troubleshooting and advising on data protection and migration.