Planning for Database Restore

Veeam Explorer for Microsoft SQL Server supports several database recovery scenarios and offers flexible options for them.

First, you should analyze how your backup infrastructure is organized and identify the locations of Veeam backup server, backup repository and production VMs. This will help you to decide on preferable machine for mount operation. For mount operations, Veeam Explorer utilizes the corresponding service (Veeam Mount Service) that can run on Veeam backup server or on Veeam standalone console - both of them include Veeam Explorer in their setup. So, Veeam backup console can work as a mount server in the remote site, eliminating the need to deploy additional Veeam backup server in that site and minimizing traffic at restore.

Consider the following recommendations:

• If repository and production (target) SQL Server VM are located in the same site with Veeam backup server, Veeam Explorer will utilize Mount Service running on Veeam backup server for mount operation.
• If repository and production (target) SQL Server VM are located in a remote site (separately from Veeam backup server), it can be reasonable to deploy Veeam Backup & Replication console in that remote site, and launch Veeam Explorer from that console, initiating mount operation locally in the site where console is running.  

Next, to choose a restore scenario, you should consider your organization’s policies and requirements and decide on the following:

1. Do you need to recover your database to the moment when the certain Microsoft SQL Server VM restore point (backup or replica) was created, or to any point in time, maybe within the interval between two restore points?  
   In the latter case, database will be restored to the closest VM restore point before the moment you specify, and then transaction log replay will bring the database to the necessary state. So, you will need to make sure Microsoft SQL Server transaction logging is enabled, and logs are backed up according to VM backup job settings
2. Should you recover to the point in time, or is it necessary to support more granular recovery and be able to roll back to a state before undesired transaction?  
   For the most granular restore, you will need to enable backup of your Microsoft SQL Server transaction logs and ensure the staging SQL Server availability and proper configuration (see the System Requirements section).
3. Do you plan to restore your database to the original Microsoft SQL Server, or to a different server?
   Make sure the account you plan to use for restore has sufficient permissions on the target server, and check for proper version (later version database cannot be restored to earlier version server).

To implement the selected scenario, you should, in particular, do the following:

• Decide on the required level of restore granularity and configure the necessary SQL Server VM backup job settings
• Use the appropriate SQL Server VM file-level recovery option when choosing VM’s restore point
• Provide the user account under which Veeam Explorer for Microsoft SQL Server will be launched with sufficient rights on your source and target SQL Servers

Considerations and Limitations

1. If you are using SQL Server Express edition (for example, SQL Server 2012 Express included in Veeam setup) as a staging system, consider that databases that exceed 10 GB cannot be attached to this SQL Server due to Express edition limitations (http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx). If you plan to recover large databases following a scenario that requires a staging server, then it is necessary to configure a fully functional commercial version of Microsoft SQL Server as a staging system.
2. By default, system databases (master, model, msdb) are skipped from transaction log processing and are not a part of Veeam Explorer restore workflow. These databases can be restored using file-level restore, as described in the Veeam Backup & Replication User Guide. If you want to exclude other database(s) from transaction log processing workflow, please refer to this Veeam Knowledge Base article: https://www.veeam.com/kb2104. (Consider that exclusion configured this way will be treated as a global setting.)
3. Currently, point-in-time restore and restore to the state before selected transaction is not supported for replica VMs and for restore points created by backup copy job.
4. Transaction log backups is not supported for Windows Server 2008 or earlier guests on Hyper-V 2012 R2.
5. If you want to restore a database from AlwaysOn availability group node to the state as of prior to selected transaction, all nodes of the group should be located in the same time zone.
6. Table-level recovery is supported only for database tables with no external dependencies.
7. If you plan to restore an encrypted database using Veeam Explorer for Microsoft SQL Server, consider information provided in this Knowledge Base article.
8. If you plan to restore database schema objects, consider that 'Replace' logic is not supported - that is, if an object with the same name exists in the production database and in the backup, then a backup object will not replace the existing one, but an object with a different name will be created instead, as described in the Restoring Database Schema and Data. Also, consider that when objects are renamed, relationships between them are not renamed.
9. If both Microsoft SQL Server and Oracle Server are installed on one VM, and this VM is processed by a job with log backup enabled for both applications, Veeam Backup & Replication will back up only Oracle transaction logs. Microsoft SQL Server transaction logs will not be processed.