709 - How to back up and restore detached SQL Server databases in ESE

SQL Server databases can be backed up using either

• file selection in the Files view, or 

• database selection in the SQL view

in the Backup Selection tab of the ESE Agent.

Note: To restore the 'master' database backed up from the SQL view, as is typically required for disaster recovery, proceed to the Restore section below.

The recommended method to back up SQL databases with ESE is to use VSS, i.e. from the SQL view. This will automatically include all metadata associated with the databases (e.g. file location and permissions). However, in the event that a database is detached, the VSS SQL backup will skip that database, even though the files still exist on disk.

Warning: Offline databases still attached to the SQL Server instance will not be backed up.

To back up

To ensure that detached databases are backed up, their database files can be backed up seperately. To do this:

1. Browse to and manually select the files (*.mdf, *.ldf) in the Files view.
2. Start the backup as you normally would.

Database files will be backed up and/or patched if there were any changes since the last VSS backup performed from the SQL view.

Note: This is not the recommended method because the database's metadata is not backed up when this method is used. 

To restore

If a restore of a specific backup is required, it will not be available in the SQL view but can still be restored on file level. To do this:

1. In the Files view of the Agent's Restore tab, browse to and select the relevant files, then click Restore.
2. Once the database files are restored:
   • To 'reattach' the master database to the new instance, follow the procedure presribed by Microsoft: https://msdn.microsoft.com/en-us/library/ms190679.aspx
   • Non-system databases must be attached manually via SQL Server Management Studio (or T-SQL).