MS SQL backup


The Backup Manager lets you back up databases powered by Microsoft SQL Server. The feature is available on Windows. The following MS SQL versions are supported:



  • MS SQL 2005

  • MS SQL 2008

  • MS SQL 2012

  • MS SQL 2014

  • MS SQL 2016


The minimum backup unit is a database. It isn't possible to exclude certain tables or files from a backup selection.



Requirements


Host system


The Backup Manager must be installed on the same MS SQL server that you want to back up.


Free space


There must be a sufficient amount of free space in the VSS Shadow Copy storage area.


MS SQL backups depend on VSS snapshots. When a backup session is completed, snapshots are automatically deleted.


Recovery model


We highly recommend setting the database to the simple recovery model before starting backups. Under this model, inactive virtual log files are automatically removed after each checkpoint (or shortly after it). It saves space and helps avoid unnecessary processing.


Here is how to access the model selection:



  1. Start the SQL Server Management Studio.

  2. In the Object Explorer, right-click a database and then select Properties from the context menu that opens.

  3. In the Compatibility level list, select Simple.



Important notes:



  • The simple recovery model makes it possible to restore a database only to the end of the most recent backup. We recommend scheduling backups frequently enough to prevent the loss of recent changes.

  • If you choose to back up a database under the full recovery model, you are responsible for truncating the logs.


Copy-only backups for MS SQL


The Backup Manager supports copy-only backups of MS SQL databases. Unlike regular backups, copy-only backups do not make any changes to the database and do not interfere with the normal sequence of backups (visit MSDN Library to learn more).


Here is how to perform a copy-only backup using the Backup Manager:



  1. Open the config.ini file and add UseCopyOnlySnapshot=1 to the [MsSql] section (more on editing config.ini).


[MsSql]
UseCopyOnlySnapshot=1


  1. Restart the internal process associated with the Backup Manager: start the Services Console (services.msc), right-click "Backup Service Controller" and then choose Restart.

  2. Perform a backup.

  3. Re-open config.ini and set the value of the UseCopyOnlySnapshot parameter to 0 (or remove the parameter from the file altogether).

  4. Restart the Backup Service Controller as described in step 2.


You can restore copy-only backups in the same way as regular backups.


Advanced details (for troubleshooting)


In most cases uncompleted backup sessions are restarted automatically. However, if an error persists, you can try these steps for troubleshooting:



  1. Make sure the required shadow copy services are running (SQL Writer Service and Volume Shadow Copy). It can be done using the vssadmin list writers command.

  2. Check for error messages in application event logs.

  3. Make sure there is enough free space in the VSS Shadow Copy storage area (otherwise the system can start deleting snapshots required for backups).


If none of the above helps, please contact support for assistance.