42648: Acronis Backup Advanced: Troubleshooting MS SQL Server Backup

Also read in:

use Google Translate

Operating Systems: 

    Last update: 29-04-2021

    Follow these steps to troubleshoot issues with single-pass backup of SQL Server databases:

    1. Get familiar with technology of single-pass backup in the About part.
    2. Check for known issues in the Known issues part.
    3. Follow to Troubleshooting part to troubleshoot and resolve the issue.

    Expand All / Hide All

    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:

    1. Open the TIB archive using any file viewer (e.g. Far Manager);
    2. 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:

    1. 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.
    2. 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.

    3. 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:

    1. Select your backup plan and click Edit button;
    2. Open backup options;
    3. Set option "Volume Shadow Copy Service" = "Software - System provider";
    4. 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.

    Tags: