Restore databases using Veeam Explorer for Microsoft SQL Server
After analyzing the theory of processes and practical tips for backing up a virtualized SQL Server, let's move on to the story of restoring databases using Veeam Explorer for Microsoft SQL Server. This tool allows you to restore the database to the original or to another server without having to lift the server itself from the backup.
In general, the recovery process with the participation of Veeam Explorer is as follows:
Of course, we are guided by the instructions of recovery policies. To select the necessary scenario and prepare everything necessary for its successful execution, it is not bad to make a checklist and answer the basic questions:
I note that in the recovery process, you do not need to completely restore and run the server itself. Instead, the backup (as it is stored in the repository, that is, after compression and deduplication) is mounted on the Veeam backup server. In order to correctly mount the file system of this virtual machine and display it in its usual form in the browser window of the Veeam Backup Browser, use the special driver Veeam.
Then you can choose which tool we will use to restore the database:
')
So let's focus on restoring the database using Veeam Explorer for Microsoft SQL Server.
Just perform 5 simple preparatory actions. I recommend to make a small checklist that will save you time and nerves in case they are especially needed.
Step 1: Check if the ports are open
To connect the Veeam backup server with the guest OS of your SQL server, you will most likely need to open TCP ports 1433 and TCP 1434 on the backup server. The rest (if necessary) will depend on the configuration of your SQL Server; For details, welcome to http://msdn.microsoft.com/en-us/library/cc646023.aspx#BKMK_ssde .
Step 2: Prepare a secondary server (if necessary)
If you need to restore the database to the state corresponding to the selected backup of the SQL server, no additional machines will be required. But if you are going to choose another scenario, then you will need a staging server, which provides the following operations (see also the table above):
By default, SQL Server installed with Veeam backup server will be used as an auxiliary. You can specify a different server by running Veeam Explorer for SQL (for example, from the Start menu) and selecting Options from the main menu.
Important! The main thing here is to specify the local SQL Server instance (i.e., it is on the same machine with Veeam Explorer and backup server) and check that its version is not younger than that of SQL, whose database you plan to restore (t. E. Cannot use SQL Server 2008 to restore / export database from SQL Server 2014).
Step 3: Assign Account Rights
At different stages of the process, the following rights may be required (if necessary, contact the DBA for issuing them):
Step 4: Examine the limitations and features of operations
Step 5: Prepare the files for recovery
To do this, you can use any recovery option from the supported Veeam Backup & Replication; The most convenient way is to use the SQL Restore Wizard (as described here ) - it will perform the necessary operations automatically and launch the Veeam Explorer itself. If necessary - for example, if you work with VeeamZIP - you can perform similar operations manually (as described here ) - then the final item will be to add the necessary database to the Veeam Explorer console using the Add Database command (for more details, see here ).
Important! Database recovery to SQL Server ( Restore menu commands) is supported for Veeam Backup & Replication Enterprise and Enterprise Plus editions. For all editions (including Free and Standard), you can perform recovery by exporting the base files and then attaching to the server you need.
Scenario number 1. Restore to the state “as in the selected SQL Server backup”
You will need a recovery point (backup, replica, VeeamZIP) created taking into account the operation of the application. Required configuration settings:
Note: For those who have forgotten or missed the description of these settings - they were described in the first series .
This scenario (if you have a Veeam Backup & Replication Enterprise or Enterprise Plus license) allows you to restore the database to the original server or to another one you have chosen.
Option 1: restore to original server
For this it is convenient to use the quick recovery 1-Click Restore, which will come with the following default settings:
Having realized these facts, we select the necessary base from the tree in the Veeam Explorer console, and from the menu select the Restore Database> Restore current_state_date to server \ instance command.
Note: Here, current_state_date is the date when the SQL server backup was created, whose contents are now mounted on the Veeam server and shown to you via Veeam Explorer; thus, there will be either “latest” - if the last backup was chosen, or the corresponding date.
Useful: You can select several databases - all databases from one instance or all databases from one server - by selecting the corresponding node (instance or server) in the tree.
Option 2: restore to selected server
To restore to the selected server (not the original), you will have to go through the steps of the wizard:
Scenario number 2. Recovery to a specified point in time
This script allows you to restore the database to your chosen point in time (which may differ from the moment of creating the backup copy of the server itself). In fact, the base will be restored to the state “as in the nearest previous backup of the server”, and then the logs will be “rolled forward” from their backup - so the base will be brought to the desired state. Therefore, to implement this scenario, you will need the following configuration settings:
Note: In the current version, this script is not supported for replicas and backups saved to the repository using archive tasks.
In this scenario, recovery to the original or selected server is also supported; You can get to the corresponding step of the Restore Wizard by selecting Restore Database> Restore point-in-time state to <server_name> or Restore to another server from the menu.
Here you will need to select the Restore to a point in time option and use the slider (“slider”) to indicate a specific point in time for which we want to restore the base.
Scenario 3. Restore to state before selected transaction
Here the user can view the list of operations for the selected period, find the desired one (for example, deleting a record from the table) and then restore the database to the state it was in before performing this operation (the full list of operations is given here ).
To implement this scenario, you will need the following configuration settings:
Note: If the restore was performed for a secondary node of the group, in some cases an error of the “The specified STOPAT time is too early” may occur. In this case, you need to perform a restore from the primary node.
A detailed description of the steps in this scenario is provided in the document “Basic Use Cases” . In short, I will only say that at the step of the Specify restore point wizard, you will need to select the Restore to a point in time option, and also check the Perform restore to specific transaction checkbox so that you can view the list of transactions at the next step Fine-tune the restore point .
In general, the recovery process with the participation of Veeam Explorer is as follows:
- The administrator responsible for the recovery starts the Veeam Backup & Replication console and uses its favorite recovery option (from the proposed Veeam) to mount the SQL Server (directly from the backup, as it is in the repository) to the Veeam backup server, along with the file system and all existing on SQL Server instances and databases.
- Veeam Explorer for Microsoft SQL Server retrieves information about the hierarchy of instances and databases on this SQL server and in its console creates a view for the user. The user can view the entire tree and, having found the required database (s), select and run the required recovery script. The following recovery and export scenarios are supported:
- To the state corresponding to the selected recovery point (the one that is currently mounted on the Veeam backup server)
- In the state at the selected time
- To the state on the eve of the selected transaction
The first of these scenarios involves only target SQL Server; for the rest, both the target server and the auxiliary SQL server (staging server) are required according to this table:Server Recovery at a time Recovery per transaction Export at time Export per transaction Auxiliary Not used Used to display logs Used by Used to display and roll up logs Trust Used for logging logs Used for logging logs Not used Not used - Finally, the database is completely “recreated” on the target Microsoft SQL Server and is ready to go. If you used the export script, you can attach the database to the server you need.
Determine what and how we want to recover
Of course, we are guided by the instructions of recovery policies. To select the necessary scenario and prepare everything necessary for its successful execution, it is not bad to make a checklist and answer the basic questions:
- Will you restore the database at the time of creating the SQL restore point, or do you need to be able to recover at any time (including between server recovery points)?
In the latter version, you will need backups of the logs to “roll” them onto the selected point, so you will need to select the Backup logs option when setting up the server backup job. - Do you plan to restore the database to a state that preceded any operation (for example, deleting a record from the table)?
To restore with this level of granularity will need:- transaction log backups
- an auxiliary SQL server (I’ll talk about the requirements for it a little later), which will allow you to display a list of operations in the console so that the user can view them and select the desired one, and then perform the logs.
- Will the database be restored to the original Microsoft SQL Server or another?
- Check the connection settings of the Veeam backup server with this server.
- Make sure that the account under which you plan to perform the restoration has all the necessary rights.
- Check the version of SQL Server where you will recover (restoring the database from a newer server to an older server will not be supported).
- Who will be engaged in database recovery? Are you personally, or other users (user groups)?
In the second case, it is reasonable to delegate the recovery, using the Enterprise Manager functionality for this.
I note that in the recovery process, you do not need to completely restore and run the server itself. Instead, the backup (as it is stored in the repository, that is, after compression and deduplication) is mounted on the Veeam backup server. In order to correctly mount the file system of this virtual machine and display it in its usual form in the browser window of the Veeam Backup Browser, use the special driver Veeam.
Then you can choose which tool we will use to restore the database:
')
- Veeam Explorer for Microsoft SQL Server - allows you to restore the database to the original or to another server (a commercial license is required) to the state corresponding to the selected backup of the server itself / specified time / on the eve of the selected transaction. I will describe this tool and scripts below in detail.
- Veeam Backup Enterprise Manager - allows you to delegate the ability to restore to other users, giving them the right to work with certain servers (an Enterprise Plus commercial license is also required). It supports recovery to the original or another server, to the state corresponding to the selected server backup / specified point in time.
- For those who are passionate, you can use the Granular Restore Wizard (U-AIR) and create a request to the backup administrator to restore the database, table or query results to the state at the time of the selected SQL server backup. Since this method involves a virtual laboratory and requires more fine-tuning, and the tool is now issued upon request, it is recommended that you contact the support service for such a case. If you are still working with version 7, then you can read the documentation .
So let's focus on restoring the database using Veeam Explorer for Microsoft SQL Server.
What should I do before launching Veeam Explorer?
Just perform 5 simple preparatory actions. I recommend to make a small checklist that will save you time and nerves in case they are especially needed.
Step 1: Check if the ports are open
To connect the Veeam backup server with the guest OS of your SQL server, you will most likely need to open TCP ports 1433 and TCP 1434 on the backup server. The rest (if necessary) will depend on the configuration of your SQL Server; For details, welcome to http://msdn.microsoft.com/en-us/library/cc646023.aspx#BKMK_ssde .
Step 2: Prepare a secondary server (if necessary)
If you need to restore the database to the state corresponding to the selected backup of the SQL server, no additional machines will be required. But if you are going to choose another scenario, then you will need a staging server, which provides the following operations (see also the table above):
- Restore base to the state on the eve of the selected transaction
- Export of the database (.MDF and .LDF files) in the state in which it was at the selected time or on the eve of the selected transaction
By default, SQL Server installed with Veeam backup server will be used as an auxiliary. You can specify a different server by running Veeam Explorer for SQL (for example, from the Start menu) and selecting Options from the main menu.
Important! The main thing here is to specify the local SQL Server instance (i.e., it is on the same machine with Veeam Explorer and backup server) and check that its version is not younger than that of SQL, whose database you plan to restore (t. E. Cannot use SQL Server 2008 to restore / export database from SQL Server 2014).
Step 3: Assign Account Rights
At different stages of the process, the following rights may be required (if necessary, contact the DBA for issuing them):
- The account that you plan to use to access the guest OS of your SQL Server (the same one that you specified in the Guest Processing step when setting up the backup job) must be in the sysadmin fixed server role on this SQL Server (if not, database contents will not be displayed in the Veeam Explorer window). See also the article Veeam Knowledge Base .
- The account under which you plan to launch Veeam Explorer - as a rule, this is the one under which the Veeam Backup Service is running, the requirements for which are described here - for the scenario with database export it should have rights to the folder to which it will go export of database files (at least Read and Write )
- The account under which the connection will be made to the target SQL Server to restore the database (this can be the original or another server) must be included in the sysadmin fixed server role on the target SQL Server.
- You may also need an account under which access to the Windows server of the server will be carried out, where backups of transaction logs will be copied for later (depending on the scenario, this will be either the target server or an auxiliary server). This account must have access to the administrative folder on the specified server (at least with Read and Write rights).
Step 4: Examine the limitations and features of operations
- In the current version, a SQL Server instance on a remote machine is not supported as a secondary server. Use only local instances.
- If you use SQL Server Express as an auxiliary (for example, SQL Express 2012 server that comes with Veeam), remember that it has a limit on the size of the databases supported - this is 10 GB. See more here .
- By default, system databases (master and others) are excluded from processing. If you need to restore them, you can use, for example, the procedure for restoring guest system files (as described here ).
- The current version does not support recovery from the replica to the selected point in time.
- If you need to restore the encrypted database, read this article from the Veeam Knowledge Base .
Step 5: Prepare the files for recovery
To do this, you can use any recovery option from the supported Veeam Backup & Replication; The most convenient way is to use the SQL Restore Wizard (as described here ) - it will perform the necessary operations automatically and launch the Veeam Explorer itself. If necessary - for example, if you work with VeeamZIP - you can perform similar operations manually (as described here ) - then the final item will be to add the necessary database to the Veeam Explorer console using the Add Database command (for more details, see here ).
So, database recovery scripts
Important! Database recovery to SQL Server ( Restore menu commands) is supported for Veeam Backup & Replication Enterprise and Enterprise Plus editions. For all editions (including Free and Standard), you can perform recovery by exporting the base files and then attaching to the server you need.
Scenario number 1. Restore to the state “as in the selected SQL Server backup”
You will need a recovery point (backup, replica, VeeamZIP) created taking into account the operation of the application. Required configuration settings:
- The recovery model for your database can be any.
- In the settings of the backup copy job in the Guest Processing step, the checkbox Enable application-aware image processing should be selected:
- In the dialog box of the virtual machine processing settings on the General tab, you need to select the following options:
- In the Applications - Require successful processing (recommended)
- In the section Transaction logs - Process transaction logs with this job (recommended)
- In the same dialog on the SQL tab, you can choose any of the log processing options (of course, with an understanding of what will happen to them during this).
Note: For those who have forgotten or missed the description of these settings - they were described in the first series .
This scenario (if you have a Veeam Backup & Replication Enterprise or Enterprise Plus license) allows you to restore the database to the original server or to another one you have chosen.
Option 1: restore to original server
For this it is convenient to use the quick recovery 1-Click Restore, which will come with the following default settings:
- The database will be restored to the state “as in the selected SQL server backup”
- Base files will be copied to the source server and then attached to the source instance (the one that was taken and now works in production)
- To connect to this server, the account under which Veeam Explorer works and Windows authentication will be used. If this account does not have enough rights, then Veeam will try to use the account you specified when configuring the backup task at the Guest Processing step. If it doesn’t work either, then you will be prompted to enter a username and password (check for admin rights!)
Having realized these facts, we select the necessary base from the tree in the Veeam Explorer console, and from the menu select the Restore Database> Restore current_state_date to server \ instance command.
Note: Here, current_state_date is the date when the SQL server backup was created, whose contents are now mounted on the Veeam server and shown to you via Veeam Explorer; thus, there will be either “latest” - if the last backup was chosen, or the corresponding date.
Useful: You can select several databases - all databases from one instance or all databases from one server - by selecting the corresponding node (instance or server) in the tree.
Option 2: restore to selected server
To restore to the selected server (not the original), you will have to go through the steps of the wizard:
- At the Specify restore point step, select the Restore to the current restore point option, i.e. restore to the state “as in the selected SQL server backup”:
- Then, at the Specify target step of SQL Server connection parameters, we specify all that is required: the name of the server to which we want to restore the database, the name of the database, the account under which we will connect to the target server. Do not forget to check that this account has the necessary rights (in fact, it was Step 3 of the list of preliminary actions above :-)).
A few explanatory words:- If we want to restore to a specific instance, we specify it in the format server / instance .
- The specified target server must have a version not lower than the original one (that is, you should not try to restore the SQL 2014 database to SQL 2008).
- (for earlier versions of SQL): check that the target database is not in mirror mode (such recovery is not supported)
- If you do not specify the name of the target database, the same name as the source one (from backup) will be used.
Important! If a database with this name exists on the target server, then by default it will be deleted, and it will be replaced by the restored one from the backup, so in this version, after clicking Next, you will need to confirm this action (or cancel and enter a new database name). - If the specified target server supports AlwaysOn Availability Groups, at the Specify Always ON restore options step, you can select the group into which we want to include the restored database:
- Next, we indicate under which account you will need to knock on the guest OS of the target server (in particular, to copy the database files there):
- Then we say exactly where to put these files (.MDF and .LDF):
- Finally, click the Restore button and wait for the report on the completion of the process.
Scenario number 2. Recovery to a specified point in time
This script allows you to restore the database to your chosen point in time (which may differ from the moment of creating the backup copy of the server itself). In fact, the base will be restored to the state “as in the nearest previous backup of the server”, and then the logs will be “rolled forward” from their backup - so the base will be brought to the desired state. Therefore, to implement this scenario, you will need the following configuration settings:
- The SQL Server recovery model must be Full or Bulk-logged .
- The SQL Server recovery point must be created taking into account the operation of the application ( Enable application-aware image processing is enabled).
- In the dialog box of the virtual machine processing settings on the General tab, select:
- In the Applications - Require successful processing (recommended)
- In the section Transaction logs - Process transaction logs with this job (recommended)
- In the processing settings of transaction logs should be selected Backup logs .
Note: In the current version, this script is not supported for replicas and backups saved to the repository using archive tasks.
In this scenario, recovery to the original or selected server is also supported; You can get to the corresponding step of the Restore Wizard by selecting Restore Database> Restore point-in-time state to <server_name> or Restore to another server from the menu.
Here you will need to select the Restore to a point in time option and use the slider (“slider”) to indicate a specific point in time for which we want to restore the base.
Scenario 3. Restore to state before selected transaction
Here the user can view the list of operations for the selected period, find the desired one (for example, deleting a record from the table) and then restore the database to the state it was in before performing this operation (the full list of operations is given here ).
To implement this scenario, you will need the following configuration settings:
- The SQL Server recovery model must be Full or Bulk-logged .
- The SQL Server recovery point must be created taking into account the operation of the application ( Enable application-aware image processing is enabled).
- In the dialog box of the virtual machine processing settings on the General tab, you need to select the following options:
- In the Applications - Require successful processing (recommended)
- In the section Transaction logs - Process transaction logs with this job (recommended)
- In the processing settings of transaction logs should be selected Backup logs .
- An auxiliary server will be required - it will display a list of transactions from the transaction log backup.
- If the base is included in the AlwaysOn Availability Group, check that all nodes in this group are located in the same time zone.
Note: If the restore was performed for a secondary node of the group, in some cases an error of the “The specified STOPAT time is too early” may occur. In this case, you need to perform a restore from the primary node.
A detailed description of the steps in this scenario is provided in the document “Basic Use Cases” . In short, I will only say that at the step of the Specify restore point wizard, you will need to select the Restore to a point in time option, and also check the Perform restore to specific transaction checkbox so that you can view the list of transactions at the next step Fine-tune the restore point .
What else to read and see
Source: https://habr.com/ru/post/259653/
All Articles