Sql-server – Why is the database restore being aborted

Assuming the row in the config table is identified by WHERE DB = @DB:

UPDATE msdb.dbo.db_restconfig 
SET FilGroups_Move_To_FullQuery = STUFF((SELECT ',MOVE N''' + LogicalName 
   +  '''TO N''' + LEFT(physicalname, CHARINDEX('.', REVERSE(physicalname)) - 1)  
   + @path_name 
   + REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\', REVERSE(physicalname), 1) - 1))  
   + ''''
 FROM msdb.dbo.restorelist_files
 FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
WHERE DB = @DB;

That won't work. As soon as you recover the database after restore, that begins a new timeline for the database.

If this is an on-going process, one possible solution is to get the team generating the backups to take a differential every day which will have fairly low impact on effort and system load. This is in addition to what they currently have. However, it will require some careful management of the files to ensure you have the right sequence of full backup, differential and tlogs. The benefit is, you will not have more than the 20 tlog files to apply if you ever need to restore the database to the latest version. You also don't lose existing PITR based on the 15-20 tlog backups taken daily (I'm assuming there is some PITR requirement given the number of log backups).

If you want to further reduce the number of log files to apply, you can increase the number of differential backups per day but I'd wager the added complexity in managing the files far outweighs the benefits. If you script all your tasks (sounds like you are), execution and scrutinizing for issues is fairly easy to do. If you're using the SSMS GUI, well, you really shouldn't.

** To make it clear, this assumes you already reset the backup chain with a full database backup at some interval. Having just a single full DB backup and only taking differentials and tlog backups from there on is not a viable solution. In most situations, the differential will eventually grow to a large size, possibly bigger than the base (full DB) backup if you have lots of write activities. An example of a basic backup cycle might look like: - Full DB backup - every Sunday 2am - Differential DB backup - every day - 8pm Transaction log backup - every 15minutes This cycle has potential data loss of up to 15 minutes if there are no other protections (e.g. AlwaysOn) in place.

If this is just a 1-time thing, i.e. you won't get any more tlog backups then you could reduce your recovery time when the goal is to get to the latest point-in-time available. Just restore the DB, differential and apply all the logs. Once done, take a backup of that and keep it along with all the other backup files you have. This buys you rapid recovery when you need to get to the latest version available since you only need to do a single DB restore. However, don't delete all the other backup files in case you need to restore to a specific point-in-time other than the most recent.