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.
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).
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:
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
order by database_name, type, backup_start_date, backup_finish_date
*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
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.
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:
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.
1. Navigate to Tasks > New Task > SQL Server Backup.
2. Click + Add Computers to detect and select the controlled computer with SQL instance.
3. Move to Step 2, and hit + Add to select instance and database for backup. You can select as many databases as you want.
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.
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.
The created backup task will show in the Tasks tab, and you can directly restore databases from it.
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.
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.