Use database backups as live SQL Server databases to reduce downtime

Being able to revert to the latest available backup immediately is a key requirement that every SQL Server DBA needs to meet. There are so many scenarios in which immediate action needs to be taken – starting from actual disk failure or corruption, system failure all the way to UPDATEs without WHERE clauses, malicious INSERTs or accidental DELETEs, DROPs or TRUNCATE operations. However, usually this is easier said than done

Even if you take meticulous care of your database backups, have the last available database backup verified and at hand and are sure that restoring it won’t overwrite any valid changes made to the database since the accident has occurred – the fact that the system will experience downtime until the backup is fully restored still remains. Luckily, downtime caused by the time needed for a full database backup restore can be minimized

The first step toward shorter restores is – don’t use the same physical disk to store the database backups and the database itself. Although this approach is usually used for security purposes, since if the disk containing the database data files fails, the database backup will still be available – its performance impact is not negligible as well. By keeping the database and its backups separate, you actually halve the I/O operations load when restoring which in turn, results in faster restores

Another practice to help you speed up your database restores is – compress your backups using SQL Server native compression. Similarly to the previous point, the benefits of this approach are twofold – not only do the backups take less disk space due to compression; they are actually restored up to 50% faster than uncompressed ones. The reason for this behavior is the fact the by default the disk subsystem is orders of magnitude slower than the memory or the CPU. As compression reduces the size of database backup this translates into less disk activity and ultimately in restoring the database more quickly

However, ultimately it boils down to this – if you are restoring a large database (e.g. 600 GB or more) the full database restore will take a significant amount of time. However, this is where ApexSQL Restore can help

ApexSQL Restore is a SQL Server restore tool which attaches both native and natively compressed SQL database and transaction log backups as live databases, accessible via SQL Server Management Studio, Visual Studio or any third party tool for a fraction of the time and disk space needed for a full restore. It allows attaching single or multiple backup files including full, differential and transaction log backups, making it ideal for continuous integration environments, quickly reverting inadvertent or malicious changes and balancing heavy report loads. For more information, visit the ApexSQL Restore product page

To use ApexSQL Restore for reducing the recovery downtime, please perform the following steps:

1. Start ApexSQL Restore
2. Click the Add a virtual database option
3. In the Restore a backup dialog, provide the following information:
   1. The name of the SQL Server instance the backup will be attached to
   2. The authentication method for that instance; using SQL Server authentication will require providing valid SQL Server credentials
   3. The name of the virtually restored SQL database
4. Click Next
5. To select the backup files to be restored click the Add files button, navigate to the patch containing those files, select them and click Open

6. Select the appropriate check-boxes next to each backup you want to attach

   

7. To attach a different backup set click Remove file and repeat the previous step
8. To specify another path that will hold the virtually restored files path click Advanced >>
9. Click Finish to complete the process

Reducing the downtime caused by database backup restores can be mitigated by separating the database files from the backup files on different disks, compressing the database backups – but if you want to really cut on recovery downtime ApexSQL Restore might be the tool you need