Restoring backups - SQL Backup

SQL Backup provides a wizard to guide you through the process of Restoring Backups. To start the Restore wizard, click Restore, or in the Activity History, right click the backup and click Restore.

The Restore wizard comprises the following steps:

Restoring multiple backups

You can restore multiple full, differential, and transaction log backups that belong to the same backup set using the graphical user interface. For example, you can restore a full and differential backup for the same database at the same time. Details of how to use the graphical user interface to do this are given above.

You can restore multiple transaction log backups using the SQL Backup command line or extended stored procedure. To do this, use wildcard characters to enter a 'file search pattern' when you specify the DISK argument for the RESTORE command. The backup files that match the wildcard characters must belong to the same backup set.

You can restore multiple encrypted transaction log backups using the SQL Backup toolkit only if they all have the same password.

To restore transaction log backups, your database must be in an unrecovered state in order that the transaction logs can be restored to it, that is, it must have been restored using the NORECOVERY or STANDBY option. SQL Backup ensures that the backups are restored in the correct sequence. For example:

SQLBackupC -SQL "RESTORE LOG pubs FROM DISK = 'C:\Backups\Pubs\Logs\*.*' "

To specify a number of folders, use the DISK command repeatedly:

SQLBackupC -SQL "RESTORE LOG pubs FROM DISK = 'C:\Backups\Pubs\Logs\*.*', DISK = 'E:\OtherBackups\Pubs\Logs\*.*' "

You can also use the SQL Backup command line or extended stored procedure to restore a database using all the latest backups for a particular database (including the latest full backup, a subsequent differential backup, and all available transaction log backups). Use the RESTORE command with the LATEST_ALL keyword. For example:

SQLBackupC -SQL "RESTORE DATABASE Sales_Prod FROM DISK = 'C:\Backups\Sales*.sqb' LATEST_ALL WITH RECOVERY "

will restore the latest full backup of the Sales_Prod database, followed by the most recent differential backup, and then finally the most recent transaction log backups.

Restoring the master database

To restore the master database, you must use the SQL Backup command line to ensure that the SQL Server is started in single user mode.

1. Stop the SQL Server service.
2. Open a command prompt window, and navigate to the SQL Server instance's Binn folder.
3. Start SQL Server in single user mode using sqlservr.exe ‑c ‑m for an unnamed instance, or sqlservr.exe ‑c ‑m ‑s {instancename} for a named instance.
4. Use the SQL Backup command line interface to restore the master database.

   For example, at the command prompt, type:

   SQLBackupC -SQL "RESTORE DATABASE master FROM DISK = 'C:\Backups\master 20070101.sqb'"

   For a named instance, use the -I parameter to specify the instance name, for example:

   SQLBackupC -SQL "RESTORE DATABASE master FROM DISK = 'G:\Backups\master 20070101.sqb'" -I Instance2

   SQL Backup restores the master database, and SQL Server automatically stops the SQL Server service when the restore operation is complete.

5. You can now start the SQL Server in normal mode.

Restoring individual database objects

If you want to restore individual objects from a SQL Backup .sqb file, use SQL Object Level Recovery Pro. This application, included with SQL Backup, can recover individual objects to a database, with potentially large savings in time and disk space. For more information, see About object level recovery.