1119 - How to backup Microsoft SQL Server databases

Redstor's Enterprise Server Edition (ESE) Agent can backup Microsoft SQL Server databases using the Volume Shadow Copy Service (VSS). As with backing up other open or locked files (i.e. files that are constantly being modified), VSS takes a snapshot which is then used by the Agent instead of live data files.

If SQL Server is installed on your machine, ESE will automatically detect this and you will see a view option for SQL as part of the user interface.

To make a backup selection of SQL databases, open the Agent and click the SQL view button on the left. The databases will be shown in the format SQL > Server Name > Instance Name > Database Name. You can have multiple instances, each with multiple databases, as seen below.

Important note: For error reporting to work properly, databases must be selected for backup manually from the SQL view (i.e. not simply included as part of Full System Backup). If the databases are selected in the SQL view, you will see an error in the logs should the SQL service become unavailable, e.g. because of the VSS writer not being present. If the databases are not selected in the SQL view, even if Full System Backup is enabled, you will not see any errors in the logs if the SQL service becomes unavailable. 

Backup inclusions/exclusions can be made at any level of the tree structure by right-click the object and choosing Include or Exclude. This action is inherited, i.e. it is applied to any object below the level where the action is being applied.

If SQL Server is installed on your machine but there is no SQL button in your Agent, this may be because

• the SQL VSS Writer Service is not running,
• a database on a local SQL Server instance has a preceding or trailing space, or
• a service logon user doesn’t have sysadmin privileges.

If you encounter other issues with backing up SQL databases, see Article 004.

It is possible to do multiple daily backups of live SQL Server databases, although this depends on the performance of the machines, the internet bandwidth, and the size of the databases. High resource usage while backups are running may slow down tasks relating to the databases. It is best to make sure the server is well resourced and/or schedule the backups at quieter periods, e.g. lunch hour.

Best practices for SQL backups

High on-disk consumption can be a problem when backing up SQL databases. To address this, we recommend the following SQL backup best practices:

• Enable changed block tracking (CBT)
• Avoid dumping databases onto the local disk before they are backed up, as this makes compression impossible.
• Avoid backing up databases in flat-file format.
• Wherever possible, use the same file names and locations for each database dump.

See also Microsoft's Best Practice recommendations for SQL Server Database Backups here.

Note:

• Databases that are detached from the SQL Server instance will not be displayed in the SQL tree view and will need to be backed up from the Files and Folders view.
• If you select a SQL database in both the SQL tree view and the Files and Folders view, only one copy of the database will be backed up.
• If you are securing your SQL databases with passwords or encryption, note that backups may take longer and involve transmitting more data because there will be greater changes to the LDF/MDF files as a result of the encryption. When restoring, these databases will be restored in their encrypted state, which means you will need the associated passwords or certificates to access the data.