We are here to help!

Find new ways to use Atera, Ask us anything.

Follow

MS SQL Backup

The Online Backup Client lets you back up databases powered by Microsoft SQL Server (Windows versions).

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

backup-selection-for-ms-sql.png

Requirements

Supported versions

The following MS SQL versions in the mainstream editions are supported:

  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Host system

The Online Backup Client 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.

This is because 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 Recovery model 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 Online Backup Client 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. You can restore copy-only backups in the same way as regular backups.

To perform a copy-only backup, do the following:

  1. Add UseCopyOnlySnapshot=1 to the Online Backup Client configuration file.
  2. Perform a backup.
  3. Change the value of the UseCopyOnlySnapshot parameter to 0 or remove the parameter from the file altogether.

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).
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request