Kilometers of logs and recovery of databases on MS SQL

Or how to easily restore databases from a long chain of backups

Introduction

If you are using SQL Server, you have probably heard about the Full and Simple Database Recovery Model. You may know that the Simple Model allows you to restore data only at the time of the backup, while Full - at any point in time, you only need to regularly make backup copies of the transaction log. However, to restore data with the Full Model, you will need to "roll" backups of transaction logs in a certain sequence. This can be done without problems using SSMS, but only on the SQL Server volume where the backups were made. To restore to another server, you will need to manually write a T-SQL script. And the longer the backup chain will be, the more the script itself will be and the more time it will take to create it. For the same reason, administrators rarely use already created backups when they need to deploy a copy of the database to another SQL Server, and prefer to create a fresh full backup. But such a procedure can be a real problem for large databases due to high server load. In addition, if the server is "down", then, as a rule, there is no time to write a long T-SQL script for recovery. At such moments, you need to do everything as quickly as possible and without unnecessary hassle.

On the Internet, including on Habré (for example, here ), you can find various methods that solve the problem of automated creation of a T-SQL recovery script. These are mainly different scripts based on backup file names or requests to the source server for the backup history (to the msdb database). In this article, I would like to review the capabilities of the XML recovery plans that appeared in Quick Maintenance & Backup for MS SQL since version 1.6.

An overview of the utility itself can be found in the article at this link or on the official site . The presence of an XML recovery plan in a network folder along with backups will not waste time preparing a T-SQL script. No matter how long the backup chain is, in a few clicks you will restore the database to another SQL Server. You can also do this on a schedule, on a test or production server, for example, to check the entire backup chain or update database copies.
')

What is an XML Recovery Plan?

An XML recovery plan is an XML file that lists backup file names in the sequence required to restore one or more databases. Sample XML content:


The XML file is always located in the root folder with backup copies and contains relative paths to the backup files. Such an organization allows not to lose relevance after copying files to another location, for example, to a network folder.

Creating an XML Plan

The program allows you to create an XML plan in two ways:

• For those who serve databases using QMB, it is sufficient to set the Create XML Recovery Plan property in the service policy. Now the XML file will be recreated every time after creating any backup in the maintenance script. If the program is configured to copy backups to a network folder, the XML-plan file will also be copied. Thus, there will always be a fresh XML recovery plan in the network folder.
  
  
• Those who already have a regular maintenance plan that creates backups can take advantage of a special task and create an XML recovery plan on schedule. In the task, you must specify the name of the XML file, the database and the connection to the folder where the XML recovery plan will be created, see the figure. In order for a task to run on a schedule, it must be included in the script.
  

Before creating the XML file, the program will determine the sequence of backup copies of the information stored in the mdsb system database, in the same way as SQL Server Management Studio does. For the first and second methods, the XML plan will contain the sequence of backups needed to restore the database to the last possible state .

An important feature is that when creating an XML plan, the program always checks for the presence of backup files. If at least one of the files is not found, the program will generate an error. In this way, the integrity of the entire chain is further monitored. If the XML plan is created using a task, you can automatically copy the missing backups from the source server. To do this, you need to install the corresponding sign in the task.

XML plan recovery

Recovery of databases on the XML plan can be performed in two ways:

1. Manually. To do this, the program has a special window that is called by the “ Restore by XML-plan” command from the context menu in the tree-like list of servers.


On the form you need to select the XML file and the database, which backups will be restored. Please note, you can restore to the same database, temporary or specified database. The recovery mode to a temporary database is convenient for checking the backup chain. The mode in the Specified database can be useful if you need to restore to a specific database, for example, with non-standard placement of its files on disks. By clicking the "Show T-SQL" button, you can view the generated T-SQL script that will be launched for recovery.


2. Automatically according to the set schedule. For example, if you need to regularly check the backup chain on a test SQL Server, or update databases. For these purposes, the program has a special task. The parameters specified in the task are almost the same as those specified on the manual recovery form.


In order for a task to run on a schedule, it must be included in the script. For example, at night. A detailed log of the recovery can be viewed in the maintenance log.

Conclusion

The mechanism of XML recovery plans in QMB is an excellent opportunity to make it much easier for administrators to recover data from kilometer logs, transfer databases to another SQL Server and check backups. The mechanism can be used even in cases where a standard Maintenance Plan is used for backup. In the future, we plan to prepare an article on how to do this with the help of the program.

If you already use QMB and did not use this feature, then rather include an XML recovery plan! We are pleased to answer your questions in the comments or by e-mail support@qmbsql.ru