Follow these steps to troubleshoot issues with single-pass backup of SQL Server databases:
- Get familiar with technology of single-pass backup in the About part.
- Check for known issues in the Known issues part.
- Follow to Troubleshooting part to troubleshoot and resolve the issue.
About single-pass backup
Acronis Backup uses single-pass backup to protect MS SQL Server databases. A single-pass backup operation creates an application-aware disk backup which enables browsing and recovery of the backed-up application data without recovering the entire disk or volume. The disk or volume can also be recovered as a whole. This means that a single solution and a single backup plan can be used for both the disaster recovery and data protection purposes.
The single-pass backup functionality becomes available by installing Acronis Agent for Microsoft SQL Server (single-pass).
During a disk backup, Agent for SQL (single-pass) adds Microsoft SQL Server metadata to the resulting backup file. By using this metadata, Acronis Backup detects and catalogues SQL Server databases. After the backup is successfully completed, the agent truncates the SQL Server transaction log if the corresponding option in the backup plan has been set.
See more about Acronis software for backing up SQL Server in following articles:
Title |
Related to |
---|---|
Difference between Acronis Backup Advanced 11.5 for SQL and Acronis Recovery for MS SQL Server | Software description |
Known Issues
Title | Error Message/Code | Related to |
---|---|---|
TAcronis Backup Advanced 11.5 for SQL: Single-Pass Backup Fails with "Unknown Win32 Error" |
A Volume Shadow Copy Service (VSS) API error has occurred. Unknown Win32 error |
Connection to instance |
Acronis Backup Advanced 11.5 for SQL: Single-Pass Backup Fails with "The value cannot be found" |
The value cannot be found. |
Software issue |
Acronis Backup Advanced 11.5 for SQL: "Cannot truncate the transaction logs of SQL database 'name' because there is no full backup of this database" |
Cannot truncate the transaction logs of SQL database 'name' because there is no full backup of this database |
Transaction log truncation |
Acronis Backup & Recovery for Microsoft SQL Server: Single-Pass Backup Ends with Warning "Microsoft SQL databases of instance '' have not been backed up since the instance is offline." |
Microsoft SQL databases of instance '' have not been backed up since the instance is offline. |
SQL Server instance not running |
Troubleshooting
Follow step-by-step instructions below to troubleshoot the issue. Complete prerequisites step before proceeding to error troubleshooting step.
1. Prerequisites
Make sure the requirements for MS SQL Server backup are fulfilled. Complete all steps before proceeding to error troubleshooting step.
1.1 Operating system should be supported
Agent for SQL (single-pass) can be installed in the following operating systems: See help topic: Supported Operating Systems.
1.2 Microsoft SQL Server version should be supported
Agent for SQL (single-pass) supports the following versions of Microsoft SQL Server: See help topic: Supported Microsoft SQL Server versions.
1.3 Agent for SQL (single-pass) should be installed
Make sure the Agent for SQL (single-pass) is installed:
- Connect the Management Console to the machine with the SQL Server.
- Go to Help -> About.
- Acronis Agent for SQL (single-pass) should be displayed.
See help topic: Installation of Agent for SQL (single-pass).
1.4 Check permissions for SQL Server backup
To successfully perform a single-pass backup of a machine that is running Microsoft SQL Server, the account under which the backup plan runs must be a member of the Backup Operators or Administrators group on the machine.
Also, this account must be granted the sysadmin role on each of the instances installed on the machine. See help topic: Permissions for SQL Server backup and recovery.
1.5 License for Agent for SQL (single-pass) should be assigned
To create a single-pass backup, a license for Agent for Windows and a license for Agent for SQL (single-pass) are required.
Agent for SQL requires one of the following licenses:
- Acronis Backup Advanced for SQL
- Acronis Backup Advanced for SharePoint
- Acronis Backup for Windows Server Essentials
- Acronis Backup Advanced for VMware / Hyper-V / RHEV / Citrix XenServer / Oracle VM
- Acronis Backup Advanced Universal License
To install Agent for Windows, use any license that enables installation of this agent.
If Agent for Windows is installed by using the Acronis Backup for Windows Server Essentials (Acronis Backup & Recovery for Server SBS license, you can install Agent for SQL (single-pass) without an additional license. Otherwise, use one of the following licenses:
- Acronis Backup Advanced for SQL Add-on
- Acronis Backup Advanced for SharePoint Add-on
To check that the license is assigned to the machine, go to Help -> About.
1.6 SQL services are running
SQL Server VSS Writer service and SQL Server Browser service should be enabled.
If all steps above have been executed and issue still persists, go to error troubleshooting step.
2. Error troubleshooting
Troubleshoot possible single-pass backup failure causes. Complete all steps before proceeding to collect information step.
2.1 Insufficient permissions
Error messages:
Cannot back up Microsoft SQL databases of instance 'MSSQLSERVER'.
Account 'NAME' cannot be found or is disabled in instance 'MSSQLSERVER'.
SQL ODBC connection error.
The credentials are invalid.
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NAME'.
Solution
During the installation, you are asked to specify the sysadmin credentials for each Microsoft SQL instance installed on the machine. These credentials are required to grant the sysadmin role to the agent service account.
You can skip entering the credentials and grant the agent the sysadmin role later by using SQL Server Management Studio or by running a T-SQL script. If you do not want to grant the sysadmin role to the agent, you will need to specify the sysadmin credentials in every backup plan.
See Web Help: Permissions for SQL Server backup and recovery.
2.2 Metadata is not collected or collected incompletely
Error message:
Microsoft SQL databases of instance 'SQLEXPRESS' have not been backed up since the instance is offline.
Solution
If the permissions are set correctly, but the backup still fails, the next most likely cause is that the metadata is not collected or is collected incompletely.
When validating the backup with incorrect metadata the product will error out with the following generic message:
An error occurred while executing the command.
TOL: Failed to execute the command. The 'Validating' command validates an entire backup archive within the specified location.
Do the following to check if the issue is with metadata:
- Open the TIB archive using any file viewer (e.g. Far Manager);
- Scroll down to the very bottom of the file contents and check if there is a line with metadata.sqlite in it. If there is, the metadata is collected correctly. If not, the issue is with metadata:
Metadata is collected Metadata is not collected
If the metadata is incorrect, you have two options:
- You can restore the SQL database as a file to a disk. And then mount it as described in MSDN: Attach a Database.
- Or redo the whole backup to make sure the metadata is correct.
(!) If metadata has been collected once, the backup will always be listed as single-pass. Even if all other backups in the chain were just disk backups. You will need to identify the slice and delete to correct this.
2.3 VSS writers/providers issue
Error message:
Cannot get SQL metadata from Volume Shadow Copy Service (VSS) since service 'SQLWriter' is disabled
Solution
To make sure the issue is VSS writers, do the following:
- In command-line, run vssadmin list writers
- If in the output it displays missing next to SqlServerWriter, then this is the issue.
To solve the issue, do the following:
- Make sure the SQL Server VSS Writer service is up and running (Start-Run -> services.msc) and the service logon is NTAUTHORITY/SYSTEM. If it is a different user, make sure the user has SYSADMIN priveleges.
- Make sure there are no spaces in the database names by running the query:
select '#' + name +'#' from sys.databases
If there are spaces (e.g. #test #), you need to remove them from the database names, See MSDN: Rename a Database.
- After removing the spaces, run the vssadmin list command again.
Error message:
Cannot collect SQL metadata because the selected VSS provider is not supported.
Solution
To solve the issue, do the following:
- Select your backup plan and click Edit button;
- Open backup options;
- Set option "Volume Shadow Copy Service" = "Software - System provider";
- Save changes and run backup plan again.
If all steps above have been executed and issue still persists, go to collect information step.
3. Collect information
Collect following information and contact Acronis Support.
3.1 Detailed issue description
Provide detailed issue description with screenshots.
3.2 Export of the backup plan
Export the backup plan as XML file.
See help topic: Export and import of backup plans.
3.3 System information
Collect system information: connect the management console to the machine with the agent, go to Help -> Collect system information.
See: Acronis Backup & Recovery 11.7/11.5: Generating System Report.