Backup and restore of MS SQL Server database(s) running on a remote machine is not supported.

User Account Privileges

Make sure the operating system account that performs the backup and restore has sufficient permission to access both SQL server and VSS.

Temporary Directory Folder

1. The temporary directory folder is used by CloudBacko Pro for storing backup set index files and incremental/differential delta files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder to be set to a local drive. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database.
2. It is recommended that the temporary directory folder should have at least free disk space of 50% of the total database size because the default Delta ratio is 50%. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
3. The SQL Windows service must have read and write permission to the temporary directory.

SQL Server VSS Writer

Make sure the SqlServerWriter has been installed and running on the SQL server, and the writer state is Stable. This can be verified by running the “vssadmin list writers” command in the Windows Command Prompt.

If you do not find the SqlServerWriter in the result, make sure the SQL Server VSS Writer has been started by following the instructions in Windows Services section below.

Example:

C:\Users\Administrator>vssadmin list writers
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001-2013 Microsoft Corp.

Writer name: 'Task Scheduler Writer'
   Writer Id: {d61d61c8-d73a-4eee-8cdd-f6f9786b7124}
   Writer Instance Id: {1bddd48e-5052-49db-9b07-b96f96727e6b}
   State: [1] Stable
   Last error: No error

Writer name: 'VSS Metadata Store Writer'
   Writer Id: {75dfb225-e2e4-4d39-9ac9-ffaff65ddf06}
   Writer Instance Id: {088e7a7d-09a8-4cc6-a609-ad90e75ddc93}
   State: [1] Stable
   Last error: No error

Writer name: 'Performance Counters Writer'
   Writer Id: {0bada1de-01a9-4625-8278-69e735f39dd2}
   Writer Instance Id: {f0086dda-9efc-47c5-8eb6-a944c3d09381}
   State: [1] Stable
   Last error: No error

Writer name: 'SqlServerWriter'
   Writer Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
   Writer Instance Id: {3de4f842-4d57-4198-9949-3b3f8c2629dc}
   State: [1] Stable
   Last error: No error

Writer name: 'System Writer'
   Writer Id: {e8132975-6f93-4464-a53e-1050253ae220}
   Writer Instance Id: {32d2fccc-624f-4baa-beb3-17b27fcae9ee}
   State: [1] Stable
   Last error: No error

Writer name: 'ASR Writer'
   Writer Id: {be000cbe-11fe-4426-9c58-531aa6355fc4}
   Writer Instance Id: {e8580fb0-b51f-40ab-91bf-4eff5107c4d1}
   State: [1] Stable
   Last error: No error

Writer name: 'WMI Writer'
   Writer Id: {a6ad56c2-b509-4e6c-bb19-49d8f43532f0}
   Writer Instance Id: {de1b6322-1d96-4f85-adbf-05cb517322ea}
   State: [1] Stable
   Last error: No error

Writer name: 'BITS Writer'
   Writer Id: {4969d978-be47-48b0-b100-f328f07ac1e0}
   Writer Instance Id: {a623b49f-a3d4-42d2-af9a-4e924fb31262}
   State: [1] Stable
   Last error: No error

Writer name: 'Registry Writer'
   Writer Id: {afbab4a2-367d-4d15-a586-71dbb18f8485}
   Writer Instance Id: {cc6b42f1-ebd0-429f-b3d3-e860905d40d3}
   State: [1] Stable
   Last error: No error

Writer name: 'Shadow Copy Optimization Writer'
   Writer Id: {4dc3bdd4-ab48-4d07-adb0-3bee2926fd7f}
   Writer Instance Id: {957ff981-d54f-4a1f-8798-bd9bd76396bd}
   State: [1] Stable
   Last error: No error

Writer name: 'COM+ REGDB Writer'
   Writer Id: {542da469-d3e1-473c-9f4f-7847f01fc64f}
   Writer Instance Id: {801fea63-6bfc-406d-9a40-4ad5af484773}
   State: [1] Stable
   Last error: No error

MS SQL Server Volumes

MS SQL Server volumes must use a file system which supports the use of VSS snapshot, for example NTFS.

Windows Services

Ensure that the following services have been enabled in the Windows Services menu.

Launch Services in Windows by clicking Start then typing “Services” in the search box. All MS SQL server related services should be started by default, in case if it is not, turn it on by right clicking the item then selecting Start.

MS SQL Recovery Model

VSS backup mode does not support backup of transaction log files, but for databases configured in either Full or Bulk-logging recovery model, this may eventually result in transaction logs filling up the available disk space on the volume of the MS SQL Server. Refer to: https://technet.microsoft.com/en-us/library/cc966520.aspx

To prevent this from occurring, you can modify the recovery model of database selected for backup to Simple.

Alternatively, to truncate the transaction log files, you can perform a transaction log backup manually, or create an additional MS SQL database backup set in ODBC backup mode to perform a transaction log backup.

Temporary Directory Folder

1. The temporary directory folder is used by CloudBacko Pro for storing the database files, incremental/differential delta files and backup set index files. To ensure optimal backup/restoration performance, it is recommended that the temporary directory folder is set to a local drive.
2. The temporary folder should not be located on Windows system partition or the database partition to minimize any potential performance impact on Windows or database. If the temporary directory folder is located on a network drive, make sure the login account has sufficient permission to access the network resources.
3. Please refer to the following URL for more details:
   https://support.microsoft.com/en-us/help/2926557/sql-server-vdi-backup-and-restore-operations-require-sysadmin-privileg
   

https://technet.microsoft.com/en-us/library/cc966520.aspx

1. It is recommended that the temporary directory folder should have at least free disk space of 150% of the total database size. The actual free disk space required depends on various factors including the size of the database, number of backup destinations, backup frequency, in-file delta settings etc.
2. The SQL Windows service must have read and write permission to the temporary directory.

Maximum Worker Thread

For SQL instance with large number of database (more than 500 databases), consider to increase the “Maximum Worker Thread” setting. Refer to the article below for further details. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option

MS SQL Recovery Model

ODBC backup mode supports transaction log backup for database with Full recovery model.

1. For database with Simple recovery mode, only full database and differential database backups can be performed. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

2. To perform a transaction log backup, please change the recovery model of corresponding databases from Simple to Full. https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server

ODBC Mode Authentication Methods

ODBC backup mode supports two types authentication method:

Trusted Authentication

This is the default authentication method in the MS SQL Server. When using this method, MS SQL Server uses the Windows login account to authenticate the login to the MS SQL Server.

MS SQL Authentication

When using this method, the username and password are created and stored in the MS SQL Server.

For details on how to verify if the login credentials you intend to use to authenticate the MS SQL Server backup job on AhsayOBM has the correct permissions, and to determine if the drive for temporary folder has enough disk space to accommodate the spooling of the database(s), please refer to Appendix E.

However, when MS SQL Server logins are used, MS SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.