Building a recovery chain for MS SQL databases

Often there is a task to restore the database from the backup chain on the backup / test server, on which there was no direct backup of the database, there are no entries in msdb, but there are backups themselves taken from the production server. The option to restore a copy of the msdb database may not be suitable if there should be different sets of jobs 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 are owned by logs. In this case, everything is trivial - both the time and the date are stored in the file name (you just remember that the time in the file names is stored in UTC). But what to do if there is not a lot of structure or files in the backups, and organizing them in a simple way is not possible or how can you simply determine starting from which log file to start donakatku? If you dealt with this issue, then you may have encountered a similar error.
Msg 4305, Level 16, State 1, Line 1
This backup set does not start at LSN 30643000001846100001, which you can apply to the database. An earlier log backup that includes LSN 30643000001845500001 can be restored.
or
In this case, you can set up terminates at LSN 9386000024284900001, A more recent log backup that includes LSN 9417000002731000001 can be restored.

In this article I will tell you how to build a recovery chain correctly 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 building logic.

1) Initially, the backup / test server database needs to generate metadata about backups.
We will fill the repository

RESTORE VERIFYONLY FROM DISK = ' ' WITH LOADHISTORY 

analogue of the famous ORACLE team
RMAN> CATALOG START WITH ...
')
This command, reading the backup from the disk, conducts the minimum necessary verification of the image correctness, and if successful, forms a record of this image in the backup server in msdb.

And the script for downloading the backup history 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 a long time (the processing time of one file is comparable to the time it takes to restore backup from this file)

The script will fill in 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 backing up is done by alternative means, we also need to download data about perfect backups. For example, recovery in Veritas NetBackup happens through the interface



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

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



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



If the recovery chain is not built, recovery is impossible for the following reasons:
- you have backups from different incarnations of the base, 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.

After specifying 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).