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.
The Backup Manager must be installed on the same MS SQL server that you want to back up.
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.
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:
- Start the SQL Server Management Studio.
- In the Object Explorer, right-click a database and then select Properties from the context menu that opens.
- In the Compatibility level list, select Simple.
- 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:
- Open the config.ini file and add UseCopyOnlySnapshot=1 to the [MsSql] section (more on editing config.ini).
- Restart the internal process associated with the Backup Manager: start the Services Console (services.msc), right-click "Backup Service Controller" and then choose Restart.
- Perform a backup.
- Re-open config.ini and set the value of the UseCopyOnlySnapshot parameter to 0 (or remove the parameter from the file altogether).
- 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:
- 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.
- Check for error messages in application event logs.
- 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.