SQL Safe InstantRestore is a powerful new restore technology that allows you to bring a database online quickly while the restore occurs in the background. SQL Safe enables the SQL Server to immediately begin the transactional part of a database restore, deferring the data file (MDF) restoration until after the database is online. SQL Server continues to handle all transaction log (LDF) restoration activity.
When the restore process is complete and the database is online, SQL Safe takes over and restores the remaining data to the data files in the background. If SQL Server needs data not yet restored, SQL Safe delivers the data to SQL Server directly from the backup. Because SQL Safe never interferes in the SQL Server log operations, ACID (Atomicity, Consistency, Isolation and Durability) compliance for your databases is not affected. When SQL Safe completes data file restoration, it removes itself from all I/O activity of the database and leaves behind a database identical to one restored with a traditional restore process. As a result, SQL Safe is no longer required to access the database.
Beginning with version 7.0, SQL Safe includes a mini-filter driver to support the InstantRestore feature. The driver, named SQL SafeFilterDriver, allows SQL Server to access database data while SQL Safe is performing an instant restore. The driver is only used during an instant restore and is no longer necessary once the database is completely restored.
How to enable InstantRestore
You first must enable the InstantRestore feature. Because some users may feel uneasy installing a device driver on their systems, InstantRestore is disabled by default. You can enable or disable the InstantRestore feature quickly depending on what task you are performing:
- If you are viewing your SQL Server instances in the Servers tree, right-click the instance you want to restore, and then select Enable SQL Safe InstantRestore or Disable SQL Safe InstantRestore.
- If you are in the SQL Safe Database Restore wizard, complete the wizard up to the Restore type tab where you will find the option for enabling InstantRestore.
If an InstantRestore operation is in progress when a user attempts to disable these components, SQL Safe displays a warning message.
Eligible backups
The InstantRestore feature is available for only a database backup that is:
- A SQL Safe backup archive with backup metadata (maps). Because InstantRestore allows SQL Server to immediately access the data in a backup, the process needs additional information about the backup which is not present in a native backup file. Please note that this information is also missing in SQL Safe backups that are written directly to Tivoli Storage Manager (TSM).
- A complete database restore. InstantRestore can restore a database using any normal restore chain starting with a full backup. InstantRestore does not support partial restores such as file restores or restoring a database with the NO RECOVERY or STANDBY options.
Monitoring your instant restores
As SQL Safe performs an instant restore, you can monitor its progress using the SQL Safe Management Console or via alerting. InstantRestore is a new type of restore operation and appears in the Management Console status grid like traditional backup or restore operations.
The InstantRestore operation is tracked with the following two operation types:
InstantRestore
The InstantRestore operation tracks the progress of the entire database restore process. The progress bar increments to 100% for the initial restore progress until the database comes online. When the initial restore completes and the database is online, the status changes to Online and the cell changes to light green. SQL Safe then displays a new line for the Hydrate operation.
Hydrate
The Hydrate operation tracks the progress of the background restore process. The progress bar increments to 100% for the background restore progress until the restore is complete. When the database restore is complete, the status of both the InstantRestore and Hydrate operations changes to Complete and the cell changes to dark green.
Instant Restore operations include the following two statuses to indicate important milestones of the operation:
Online
The Online status indicates that the database is online and ready for use.
Halted
The Halted status indicates that an event interrupted the InstantRestore process. A network issue between SQL Safe and the backup archive can interrupt an instant restore. Because InstantRestore allows changes to the database while the restore is occurring, the database is not deleted if an issue occurs during Hydration. If such an event occurs, the database transitions to a read-only state to prevent the system and users from writing additional data to the database. At this point, you can restore access to the backup archives and the instant restore can safely resume.
Handling errors during Hydration
If the hydration process is interrupted for any reason:
- The InstantRestore and Hydration operations transition to the Halted state.
- SQL Safe displays an error message stating that hydration is interrupted.
If an error occurs during the InstantRestore operation prior to the beginning of the Hydrate process, SQL Safe displays only the InstantRestore operation with an error status, and includes the error message for the failure.
The InstantRestore operation has two phases. In the first phase, the T-SQL restore command runs and after the database is online, hydration starts. If an error is encountered in the first phase (i.e. the T-SQL restore command) and the database remains in SQL Server, SQL Safe does not delete the database.
SQL Safe includes the following failure scenarios that may occur during an instant restore.
| Component | Failure | Resolution |
|---|---|---|
| Server | Crashes | If the server suffers a catastrophic crash and is no longer available, no recovery is available. |
| Server | Reboots | If the server reboots because of a power failure, automatic software update, or other similar situation, and comes back online correctly, the SQL Safe Filter Service restarts and then resumes hydration. |
| Server | Runs out of resources | If the server runs out of memory or other resources, and the SQL Safe Filter Service cannot allocate the additional resources during hydration, SQL Safe uses the following steps:
|
| Server | Suffers a disk failure | If the database or InstantRestore support files is corrupted by a disk failure, no recovery is available. |
| Service | Restarts | If one or all of the following items restarts, hydration should resume after the restart is complete:
If the database did not go into Suspect mode during this process, hydration resumes from the point where it left off when the SQL Safe Filter Service restarted. If the database is in Suspect mode, the SQL Safe Filter Service brings the database out of Suspect mode, and then resumes hydration. |
| Filter Service | Crashes | If the SQL Safe Filter Service crashes, hydration resumes once the service restarts. If the SQL Safe Filter Service crashes again, you may need to recover any new data added since the first crash. |
| Backup file | Is corrupt | If the backup file is corrupt or there is a read problem when accessing the network, the SQL Safe Filter Service fails to decompress during hydration. If the backup file is corrupt, and you have another copy of the backup file, you can restart hydration using the non-corrupt backup file. If the issue is a read problem when accessing the network, you can restart hydration once you address the network issue. |
| Backup file | Is inaccessible due to a network failure | If the backup file is inaccessible due to a network failure, InstantRestore attempts a retry. If the retry fails, and the maximum retry attempts is reached, the Hydration operation status transitions to the Failure state. Once you correct the network issue or relocate the backup file, you can resume hydration. If the Hydration fails, you may need to restart InstantRestore. |
| Backup file | Is inaccessible due to a lack of access permissions | If the SQL Safe Filter Service restarts and is unable to open the backup file because the account attempting to read the file does not have the proper permissions, you must provide the account permission, and then resume hydration. |
Does SQL Safe include new characteristics specific to the InstantRestore feature?
Yes, there are new details in SQL Safe to support InstantRestore. For supported platforms, the following components were added to SQL Safe to support InstantRestore:
SQL Safe Filter Service (SQL SafeFilterService.exe)
The SQL Safe Filter Service is responsible for handling I/O requests from SQL Server and performing the background database restore (hydration).
SQL Safe Filter Driver (SQL SafeFilterDriver.sys)
The SQL Safe Filter Driver is responsible for intercepting I/O requests for databases that have active InstantRestore operations under way. When an instant restore completes the driver totally disengages from all I/O activity of the database and is no longer needed. This device driver utilizes the Microsoft mini-filter driver technology.
Do you have to use the console for InstantRestore?
No, the Console is not the only place where you can use the InstantRestore feature. You can execute an InstantRestore via T-SQL script using either the SQL Safe CLI or XSP commands. To use the XSP InstantRestore command, see the sample XSP scripts available from the Programs menu.
Example CLI code snippets that use the InstantRestore command
You can also perform an instant restore through the CLI. Additional options can be set in the SQL Safe Restore wizard, from which you can generate a CLI script that includes the specified wizard settings.
SQLsafeCmd.exe InstantRestore <database> <full_backup> -diff <diff_backup> -log <log_backup>
The following three options are specific to a backup set:
- BackupFile (if the backup set is striped).
- BackupSet.
- Password (or EncryptedRestorePassword).
Where these options appear in the command determines to which backup set they are applied. When you encounter one of these options, it is applied to the full if no -Diff/-Log option is yet encountered, otherwise it is applied to the most recent -Diff/-Log. For example, if you want to instantly restore the following backups:
- Full backup, 2 stripes, backupset 2, encryption key "full".
- Diff backup, 2 stripes, backupset 3, encryption key "diff".
- Log backup, 2 stripes, backupset 4, encryption key "log".
Use the command:
SQLsafeCmd InstantRestore Northwind "C:\Backup\Northwind_Full (1 of 2).safe" -BackupFile "C:\Backup\Northwind_Full (2 of 2).safe" -BackupSet 2 -Password "full" -Diff "C:\Backup\Northwind_Diff (1 of 2).safe" -BackupFile "C:\Backup\Northwind_Diff (2 of 2).safe" -BackupSet 3 -Password "diff" -Log "C:\Backup\Northwind_Log (1 of 2).safe" -BackupFile "C:\Backup\Northwind_Log (2 of 2).safe" -BackupSet 4 -Password "log"
For more information about available instant restore options, see the usage statements in the CLI Help. To view the CLI Help, type SQLsafeCmd help instantrestore.