SQL Server databases can be backed up using the file selection from the Files view or database selection from the SQL view in the Backup Selection tab.
Note: Typically during a disaster recovery, to restore the 'master' database previously backed up from the SQL view, 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 the metadata associated with the databases (e.g. file location, permissions). But in the event that a database is detached, the VSS SQL backup will skip the 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 safeguard detached databases from not being backed up, database files can also be backed up seperately:
Note: This is not the recommended method because the database's meta data is not backed up. The ESE backup log will indicate that backing up using the SQL view is best.
- Browse to and manually select the files (*.mdf, *.ldf) in the Files view.
- Start the backup as per normal.
Database files will be backed up and/or patched if there were any changes since the last VSS backup performed from the SQL view.
If a restore of that specific backup is required, it will not be available via the SQL view but can still be restored on a file level:
- Browse to and select the files in the Files view - this time under the Restore tab.
- 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).
(See the following Microsoft article for steps to attach databases: https://msdn.microsoft.com/en-us/library/ms190209.aspx)
Old Article ID: 314
Previous Views: 276
Posted: 12 Jun, 2015 by Van Rensburg J.