SQL Server Backup Failed | Solutions and Alternative

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

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).

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.

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.

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.

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.

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.

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.

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:

👉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.

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 -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

👉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.

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

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.

4. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly. Whether it's full, differential, or incremental backups, AOMEI Cyber Backup provides flexible options tailored to meet diverse SQL backup needs.

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.
• Archive: You can tick the option "Archiving backup to Amazon S3" to save your backup to an Amazon S3 storage.

AOMEI Cyber Backup is a comprehensive SQL backup software providing data protection for SQL databases, ensuring business continuity and data integrity. It is suitable for businesses of all sizes and allows you to create a reliable backup solution and never have to worry about data loss.

Supplement: Tips to protect your SQL database better

The above analyzes 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.