Restoring SQL databases and transaction logs
The SQL Agent lets you restore SQL Sever databases. You can restore the databases to their original location or you can redirect the restore to a new location. The number of jobs you decide on depends on the types of backup jobs that protect the database. If you use one job to restore a database, select all the backup sets that you want to apply. Include the full backup, any differential backups, and any log backups.
With very large databases this process can take several hours to complete. During this time Backup Exec reports that no data is being transferred, and the Byte count field in the Job Monitor view is not updated. When SQL has completed filling the files with zeros, the restore job continues. This occurs for all database restores but is noticeable only on very large databases.
Restoring encrypted SQL databases
SQL 2008 supports Transparent Database Encryption (TDE), which lets you encrypt SQL 2008 databases at the backup set level.
When you back up a database that uses TDE, Microsoft recommends that you back up the certificate keys and encryption keys with the database. If you do not include the certificate keys and encryption keys, you must perform all backup and restore operations within the selected SQL instance.
Note: Backup Exec can redirect the restore of the database data that used TDE only if the certificate keys and encryption keys are applied to the destination instance. If the certificate keys and encryption keys are not applied to the destination instance, an error appears stating that the certificate thumbprint cannot be found. See your Microsoft SQL 2008 documentation.
How to restore from SQL transaction logs up to a point in time
You can restore transactions from a transaction log up to and including a point in time in the transaction log. After the point in time is reached, recovery from the transaction log is stopped. To find dates and times of transactions, check your client application event log.
If the specified point in time is later than the time contained in the most recent transaction log being restored, then the restore operation succeeds, but a warning is generated and the database remains in an intermediate state. If the specified point in time is before the time contained in the transaction log or logs being restored, no transactions are restored.
How to restore from SQL transaction logs up to a named transaction
You can restore transactions from a transaction log up to and including a named transaction (or mark). After the named transaction is reached, recovery from the transaction log is stopped.
Since named transactions do not necessarily have unique names, you can also specify a date and time after which the restore operation is to search for the named transaction. For example, if you specify a restore from a log up to the named transaction AfternoonBreak, found after 6/02/2000, 12:01 p.m., then the restore operation will not search for AfternoonBreak until after that time. To find dates and times of named transactions, check your client application event log.
If the named transaction is not found, then the restore operation succeeds, but a warning is generated and the database remains in an intermediate state.
Note: The names of transactions are case-sensitive. Ensure you enter the correct upper- and lower-case characters when specifying a named transaction.
Redirecting restores of SQL
You can redirect the following:
-
A database backup to a different server, database, or instance.
-
Differential and log backups to wherever the associated database is restored.
-
A database from a 32-bit or 64-bit platform to any other platform.
You can use both single-job restores and multiple-job restores in redirected restore operations.
To restore SQL databases and transaction logs
-
On the Backup and Restore tab, right-click the server for which you want to restore data, and then click Restore.
-
Select Microsoft SQL Server databases, and then clickNext.
-
Follow the Restore Wizard prompts to restore the data.