- Overview
- Restoring Microsoft SQL Database to the Time of a Backup
- Restoring Databases to a Specific Point in Time
- Restoring SQL Databases to a Different Instance, Folder, or Database Name
Overview
Macrium Reflect Server Plus LTSC can create and restore granular backups of Microsoft SQL databases. This enables individual databases to be restored without affecting other SQL databases in the same instance.
If SQL backups have not already been created, please view this article for more information on backing up Microsoft SQL databases.
Restoring Microsoft SQL Database to the Time of a Backup
SQL backups can be restored using the 'SQL' tab under the 'Existing Backups' tab in Macrium Reflect Server Plus LTSC.
The existing backups will be shown on this page by default. If no backups are displayed, select 'Folders to search' and enter the location of the SQL backups.
The left-hand tree shows the databases that can be restored:
Using the checkbox, shown next to each database, select the database(s) that will be restored. Multiple databases can be selected.
The middle section of the page displays the restore controls, enabling the selection of the destination instance, database name, point in time, and directory that contains the MDF and LDF files. By default, the original instance, database name, and directory will be selected. The latest backup will also be selected.
Selecting 'Select time...' enables the database to be restored to an earlier backup. In the window that opens, select 'Restore to time of backup' and the backup that will be restored.
In some circumstances, the SQL database can also be restored to a specific point in time using the 'Restore to a specific date and time' option. Read more about this here.
As databases and the backups that will be restored are selected, they will be displayed in the 'Restore Summary' section of the page, showing the databases that will be restored and the date and time they will be restored to.
Once the relevant options have been specified, select 'Restore'.
In the window that opens, a summary of the restore will be displayed. Select 'Cancel' to exit the window to change the restore options, or select 'Restore' to start the SQL database restore:
A warning box will be displayed to warn that data may be lost when restoring databases to an earlier point in time:
The progress of the restore will be displayed in this window:
Once the restore has been completed, the following message will be displayed:
Restoring Databases to a Specific Point in Time
In some circumstances, it is possible to restore a database to a specific point in time, which does not necessarily need to be the time a backup was created. This is especially useful when a table or other specific piece of data has been deleted. The following situations must be considered when trying to restore to a point-in-time:
- The database must be running the FULL recovery model.
- It is not possible to restore to a point in time before the last full or differential backup.
- It is not possible to restore to a point-in-time between full or differential images. You can restore to a point-in-time from the last full or differential backup to the present.
To restore databases to a specific point in time, after selecting the relevant database(s), click 'Select time...'.
The window that opens, select 'Restore to a specific date and time'. A specific date and time can then be entered that which the selected database will be restored to. It is not possible to set the controls to a date/time that can not be restored.
From this point, the restore process is the same as detailed above.
Restoring SQL Databases to a Different Instance, Folder, or Database Name
It is possible to restore a backup to a different instance by selecting the target instance in the 'Instance' drop-down combo box. Be aware that there can be compatibility issues when restoring databases to Microsoft SQL instances of differing versions.
By default, the folder selected in 'Restore to directory' is the default folder where the instance places MDF and LDF files associated with the databases. If you change the folder, upon restoration, the MDF and LDF files move to the target directory, wherever they were to start with.
By default, Macrium Reflect restores the database back to the original database name. However, if you change the name of the database in 'Target database name', a new database of that name is created. The MDF and LDF files that are restored are also renamed. This ensures the original database does not change, but can be deleted manually when it is no longer required.
