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.

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 you have Full System Backup enabled, all the databases will be included in the backup selection by default. If you don’t wish to backup a specific database, you can apply a manual exclusion. 

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.

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.