This is an old revision of the document!
You are strongly recommended to configure or check all the requirements below before you proceed with the MS SQL server backup and restoration.
CloudBacko Pro supports 2 backup modes when creating a backup set for MS SQL server, VSS mode and ODBC mode.
The VSS-based backup utilizing the Microsoft SQL Server VSS Writer to obtain a consistent snapshot of the MS SQL databases, no spooling / staging of database file(s) is required during the backup process.
Make sure the latest version of CloudBacko Pro has been installed on the MS SQL server.
Make sure the Microsoft SQL Server backup module has been purchased for your CloudBacko Pro license.
The continuous backup add-on module is required if you would like to enable the continuous backup feature.
The default Java heap size setting on CloudBacko Pro is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
Make sure the operating system account that performs the backup and restore has sufficient permission to access both SQL server and VSS.
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 must use a file system which supports the use of VSS snapshot, for example NTFS.
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.
Make sure the MS SQL entry is present in the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL”. To access this path, type “regedit” in the command prompt to launch the Registry Editor.
Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
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.
By using the ODBC mode for MS SQL backup, databases files are spooled to a temporary directory before being uploaded to the backup destination.
Make sure the latest version of CloudBacko Pro has been installed on the MS SQL server.
Make sure the Microsoft SQL Server add-on module was purchased for your CloudBacko Pro license.
The default Java heap size setting on CloudBacko Pro is 2048MB. For MS SQL Server backup it is highly recommended to increase the Java heap size setting to be at least 4096MB to improve backup and restore performance. The actual heap size is dependent on amount of free memory available on your MS SQL server.
https://technet.microsoft.com/en-us/library/cc966520.aspx
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.
Make sure the MS SQL entry is present in the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL”.
To access this path, type “regedit” in the command prompt to launch the Registry Editor.
Note: Pay extra attention when you are checking configuration in Registry Editor. Any unauthorized changes could cause interruption to the Windows operation.
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
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