MS SQL Database Recovery Chaining

Often there is a task to restore the database by the backup chain on the backup / test server, on which the direct backup of the database was not performed, there are no entries in msdb, but there are backups taken from the productive server. The option of restoring a copy of the msdb database may not be suitable if there should be different sets of workstations for the main server and the one on which we plan to restore. If there are few files with backups, then restoring the logical order of the files is not difficult, especially if the backups belong to logshipping. In this case, everything is trivial - the time and date are stored in the file name (you just need to remember that the time in the file names is stored in UTC). But what if there is no structure in the backups or there are a lot of files, and it’s not possible to organize them in a simple way, or how can one simply determine starting with which logshipping file to start the donation? If you dealt with this issue, then perhaps you have encountered a similar error
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 30643000001846100001, which is too recent to apply to the database. An earlier log backup that includes LSN 30643000001845500001 can be restored.
or
The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.

In this article I will tell you how to correctly build a recovery chain with a minimum of manual work and try to avoid such errors. The trick is to populate the recovery repository and use the Management Studio recovery chain logic.

1) Initially, in the database of the backup / test server, it is necessary to generate metadata about backups.
We will fill the repository

RESTORE VERIFYONLY FROM  DISK = 'Имя бэкапа' WITH  LOADHISTORY 

analogue of the well-known command ORACLE
RMAN> CATALOG START WITH ...

This command, reading the backup from the disk, conducts the minimum necessary check for the correctness of the image, and if successful, forms a backup server record in msdb about this image.

And the script for loading the history of backups from a specific folder will look like this:
(it can be supplemented with logic for processing subdirectories)

declare @Path nvarchar(255)
declare @Name nvarchar(255)
select @Path = N'\\ServerName\D$\LogShipingDir\DevDB\'
IF OBJECT_ID('tempdb..#filetmp') IS NOT NULL  DROP TABLE #filetmp ;
create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL ) 
insert #filetmp 
EXECUTE master.dbo.xp_dirtree @Path, 1, 1 
DECLARE @filename varchar(200)
DECLARE @SQL nvarchar(300)
DECLARE FileList_Cursor CURSOR FAST_FORWARD FOR
  select name from #filetmp where IsFile=1 and name like '%DevDB%'
OPEN FileList_Cursor;
FETCH NEXT FROM FileList_Cursor
INTO  @filename;
WHILE @@FETCH_STATUS = 0
BEGIN
    set @SQL=@Path+@filename;
	print @SQL;
	RESTORE VERIFYONLY FROM  DISK = @SQL WITH  LOADHISTORY
    FETCH NEXT FROM FileList_Cursor
    INTO  @filename;
END;
CLOSE FileList_Cursor;
DEALLOCATE FileList_Cursor;

! Caution: the script will run for quite some time (the processing time of one file is comparable to the recovery time of a backup from this file)

The script will fill the system tables with information about backups. The same addition to the repository occurs during normal recovery from backups. This is suitable when using backup systems with non-standard recovery methods for entering information into the recovery repository.
1.a) Otherwise, when the backup is done by alternative means, we also need to download data about the perfect backups. For example, recovery in Veritas NetBackup occurs through the interface.



At this stage, it is important to restore the base with the NORECOVERY parameter if we plan to restore the backup chain further

As a result of recovery in the repository, the VDI device will be the device on which the backup will lie and it will be impossible to access it from the SQL server, but we need this entry as a starting point for the recovery chain



2) After filling the msdb recovery repository, you can start recovery.
In Management Studio, open the recovery window, select the base for which we populated the recovery repository. The interface will attempt to build a recovery chain for one incarnation of the database - based on the LSN chain in the loaded metadata. Information about backups for building a list should be as complete as possible and contain the entire chain.



If the recovery chain has not been built, recovery is not possible for the following reasons:
- you have backups from different incarnations of the database, or
- there is no full backup to start the recovery chain.
incomplete chain may be caused by the absence of any file, or an error in the image.

Having specified all the necessary parameters, we will save the recovery script and delete the steps that we have already done, restoring from a non-standard source (for example Veritas Netbackup).