Backing up and Restoring Microsoft SQL Server Databases - Evaluator's Guide for Microsoft Hyper-V

https://helpcenter.veeam.com/evaluation/backup/hyperv/en/sql_backup_and_restore.html

Backing up and Restoring Microsoft SQL Server Databases

To protect a virtualized Microsoft SQL Server, you can configure a backup job that will create image-level VM backups and, in addition, copy database transaction logs. Image-level backups will capture the VM state at specific points in time. Transaction logs will keep records of all transactions performed against protected databases since the moment of the last backup. If a Microsoft SQL Server VM fails, you can recover the VM from the necessary restore point and then apply transaction logs to get databases on the Microsoft SQL Server to the required state between backups.

To configure a backup job that copies transaction logs, you must create a regular backup job and specify advanced settings for transaction logs shipping. In these settings, you define:

• How often you want to back up transaction logs
• How transaction logs must be shipped to the destination
• How long transaction logs must be retained

With these settings enabled, Veeam Backup & Replication actually creates two jobs linked with each other:

• A regular backup job responsible for creating image-level backups
• An auxiliary job responsible for shipping database transaction logs

The regular backup job runs by the defined job schedule. It creates image-level backups and saves them on the backup repository. After the image-level backup has been successfully created, Veeam Backup & Replication truncates transaction logs on the virtualized Microsoft SQL Server.

The auxiliary backup job runs continuously. The job copies transaction logs accumulated between VM restore points at scheduled intervals, for example, every 15 minutes. As a result, on the backup repository you will have a chain of restore points and a set of transaction logs that cover intervals between these restore points.

Veeam Backup & Replication ships transaction logs to the backup repository and saves them in files of VLB format next to VM image-level backups. To ship transaction logs from the virtualized Microsoft SQL Server to the backup repository, Veeam Backup & Replication uses log shipping servers — Microsoft Windows machines added to the backup infrastructure. You can select explicitly what log shipping servers you want to use, or let Veeam Backup & Replication assign log shipping servers automatically.

For restore operations, Veeam Backup & Replication offers a special tool — Veeam Explorer for Microsoft SQL Server. Veeam Explorer for Microsoft SQL Server is integrated with Veeam Backup & Replication. The explorer is installed automatically when you deploy Veeam Backup & Replication.

Veeam Explorer for Microsoft SQL Server supports a number of restore scenarios:

• You can restore a database to a specific point in time or to a specific transaction.
• You can restore a database to a specific point in time or transaction and export it to the necessary location.

Evaluation Case

In this exercise, you will back up a Microsoft SQL VM and restore a database on the Microsoft SQL Server to a specific transaction using the created backup. To do this, you will perform the following actions:

1. Configure a backup job that will create a Microsoft SQL VM backup and copy transaction logs.
2. Use the created VM image-level backup and transaction logs to recover a database on the Microsoft SQL Server to a specific transaction with Veeam Explorer for Microsoft SQL Server.

It is strongly recommended that you use a non-production Microsoft SQL Server with a sample database for this exercise.

Prerequisites

Make sure that the Full or Bulk-logged recovery model is enabled for the database on the Microsoft SQL Server that you plan to back up. If the recovery model is set to Simple, Veeam Backup & Replication will not detect and process transaction logs.

Procedure

Step 1. Configure the backup job for a Microsoft SQL VM

1. In Veeam Backup & Replication, configure a backup job that processes a Microsoft SQL Server VM.

For details, see the Performing Backup exercise.

2. At the Guest Processing step of the New Backup Job wizard, select the Enable application-aware processing check box. In the Guest OS credentials section, specify a user account to connect to the VM guest OS. The user account must have the sysadmin privileges on the Microsoft SQL Server. In the opposite case, Veeam Explorer for Microsoft SQL Server will fail to automatically identify Microsoft SQL databases in the created VM backup.
3. Click Applications.
4. Select the Microsoft SQL Server VM in the list and click Edit.
5. In the Transaction logs section, make sure that the Process transaction logs with this job option is selected.

6. Open the SQL tab.
7. Select the Backup logs periodically option.
8. In the Backup logs every <N> minutes field, specify how often you want to ship transaction logs from the Microsoft SQL Server VM to the backup repository. By default, Veeam Backup & Replication ships transaction logs every 15 minutes.
9. In the Retain log backups section, specify the retention policy for transaction logs. You can store them for a specific number of days or until a preceding image-level backup is removed from the backup chain.
10. In the Log shipping servers section, leave the Automatic selection option selected. Veeam Backup & Replication will automatically identify the least loaded Microsoft Windows server in your backup infrastructure and use this server to ship transaction logs to the backup repository.

11. At the Schedule step of the wizard, define scheduling settings for the job. If you do not specify that the job must run automatically by the defined schedule, the backup job will be unable to ship transaction logs to the backup repository.
12. Finish working with the wizard and run the job to produce an image-level backup of the Microsoft SQL Server VM.
13. When you create a backup job that processes a Microsoft SQL Server VM and enable transaction log shipping, Veeam Backup & Replication creates two jobs: one processing the Microsoft SQL Server VM and the other one shipping transaction logs. In the inventory pane of the Home view, expand the Last 24 hours node to see the created jobs.

14. After the image-level backup has been created, perform some transaction on the database on the Microsoft SQL Server VM that you have backed up. For example, if you use a test database, you can manually run a simple Microsoft SQL script to insert a record into the database or drop a record.
15. Wait for the period of time that you have defined in the Backup logs every <N> minutes field. After this period has expired, Veeam Backup & Replication will ship transaction logs and store them in a file of VLB format on the target backup repository, next to the image-level backup of the Microsoft SQL Server VM.

Step 2. Recover a database to a specific transaction

1. Open the Home view.
2. In the inventory pane, click the Backups node.
3. In the working area, expand the backup job processing the Microsoft SQL Server VM, right-click the Microsoft SQL Server VM and select Restore application items > Microsoft SQL Server databases.

4. Pass through the steps of the Microsoft SQL Server Database Restore wizard: select a restore point and specify a restore reason. At the last step of the wizard, click Finish to start the recovery process. Veeam Backup & Replication will automatically mount the Microsoft SQL Server VM file system to the Veeam backup server, locate the Microsoft SQL database and attach it to a staging Microsoft SQL Server — Microsoft SQL Server on which the Veeam Backup & Replication database is deployed. After that, Veeam Backup & Replication will automatically open the database in Veeam Explorer for Microsoft SQL Server.
5. In the left pane of Veeam Explorer for Microsoft SQL Server, right-click the necessary database and select Restore point-in-time state to <Microsoft SQL Server\Instance Name>.

6. Veeam Backup & Replication will launch the Restore wizard. At the Specify restore point step of the wizard, select Restore to a specific point in time. Use the slider below to define the exact point in time to which you want to restore the database.
7. Select the Perform restore to the specific transaction check box and click Next.

8. At the Fine-tune the restore point step of the wizard, select a transaction to which you want to restore the database and click Restore.
9. Veeam Backup & Replication will start restoring database to the selected transaction. When the restore process is complete, Veeam Explorer for Microsoft SQL Server will display a popup message to notify you of the restore operation results.

Validation

Check the state of the restored database on the Microsoft SQL Server VM and make sure it has been restored to the necessary state.

Elyssa D. Durant 

Research & Policy Analyst

Columbia University, New York