August 13, 2019 at 9:15 pm
Hi,
Due to vendor app issues, I've been needing to restore a small (200MB) database several times to a point-in-time. The DB is in full-recovery mode. There are no differentials, no log shipping, but the vendor will run copy-only backups occasionally.
Prior to restoring, I take a full backup in case something goes wrong with the restore, I can at least get back to where I was (admittedly, I'm rethinking this action). Post-restore, I take another full backup to initialize the log for transaction log backups.
The first point-in-time restore always works. If I need to do another, SSMS (17.9 or 18.2) refuses, claiming the LSN chain is broken. It's not, as I manually build the restore script using the last full and the subsequent logs. So I'm looking to build a query that will give me the files needed for restoring to a point-in-time.
I join msdb.dbo.backupset for the backups to msdb.dbo.backupmediafamily for the filenames on media_set_id. But how does one know where to start and stop? I've tried using the database_backup_lsn to gather other log backups with the same, as well as the full that has the same checkpoint_lsn. While that works most of the time, I can get an extra log in there if the automated backups kick in before I get my post-restore full done.
I do have my incorrect SQL I could post, but someone has to already have done this, right?
TIA,
Rich
August 13, 2019 at 9:35 pm
Is there a reason you are not using SQL Server Management Studio to do your database restore? The GUI provides an excellent interface for point in time recovery, automatically listing and including the appropriate full and log backup files. It even has a nice graphic display of the timeline and allows specification of the date and time you want to recover to.
August 13, 2019 at 9:41 pm
Hi,
As I mentioned, SSMS incorrectly claims the LSN chain is broken, so I'm needing to bypass it.
Thanks,
Rich
August 22, 2019 at 8:44 am
--This will get the latest backup file and all the log files to the latest one.
--It creates code so you can determine where the restore point ends.
Use msdb
go
DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
Declare @BackupSetID int
Set @databasename =' ' --set databasename here
Set @BackupSetID = (select top 1 bs.backup_set_ID from backupmediafamily bmf
inner join backupset bs on bs.media_set_id = bmf.media_set_id
where type ='D'
and database_name =@databasename
and physical_device_name not like '%{%'
order by backup_start_date desc
)
-----FIND BACKUP SET THE VALU BELOW.
SELECT @backup_set_id_start = @BackupSetID--Change BACKUP_SET_ID
FROM msdb.dbo.backupset b
WHERE database_name = @databaseName AND type = 'D'
SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start
IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
-----------------------------------------------------------------------------------------------------------
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
----------SET
AND b.backup_set_id >= @BackupSetID AND b.backup_set_id < 999999999 ---Change BackUp_SET_ID
AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName+ ' WITH RECOVERY'
ORDER BY backup_set_id
--physical_device_name
August 22, 2019 at 8:54 am
-- Added in Line --and is_copy_only =0
Use msdb
go
DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
Declare @BackupSetID int
Set @databasename =' ' --set databasename here#
Set @BackupSetID = (select top 1 bs.backup_set_ID from backupmediafamily bmf
inner join backupset bs on bs.media_set_id = bmf.media_set_id
where type ='D'
and is_copy_only =0 --added in
and database_name =@databasename
and physical_device_name not like '%{%'
order by backup_start_date desc
)
-----FIND BACKUP SET THE VALU BELOW.
SELECT @backup_set_id_start = @BackupSetID--Change BACKUP_SET_ID
FROM msdb.dbo.backupset b
WHERE database_name = @databaseName AND type = 'D'
SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start
IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999
SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
-----------------------------------------------------------------------------------------------------------
SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
----------SET
AND b.backup_set_id >= @BackupSetID AND b.backup_set_id < 999999999 ---Change BackUp_SET_ID
AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName+ ' WITH RECOVERY'
ORDER BY backup_set_id
--physical_device_name
August 23, 2019 at 6:46 pm
Hi Super Cat,
Thanks for the script, but this only does a complete restore and not a point-in-time one. SSMS can create this for me easily. It's when there's multiple restores to the same point-in-time with full backups in between that SSMS gets confused.
So, for better or for worse, here's my script. It's based on empirical data and has the air of being easily broken. I certainly hope that SQL Server recovery is not actually based on this method, because it seems very sloppy to me:
DECLARE @DBToRestore nvarchar(128)
DECLARE @PITDateTime varchar(128)
-- @DBToRestore is the the database name requiring a restore.
SET @DBToRestore = '' -- Enter DB Name here
-- @PITDateTime in 'yyyy-mm-dd hh:mi:ss(24hr)' format
SET @PITDateTime = '' -- e.g. '2019-08-12 15:48:00'
BEGIN
-- Start with the first backup taken after the PIT (i.e. the one that contains the proper database backup LSN).
-- If this returns no rows, the result set will be NULL and the database is not recoverable to that PIT.
WITH
cte_lsn(database_backup_lsn, last_lsn, backup_start_rank)
AS
(
SELECT TOP 1
bs.database_backup_lsn,
bs.last_lsn,
rank() over (order by bs.backup_start_date) backup_start_rank
FROM
msdb.dbo.backupset bs
WHERE
bs.database_name = @DBToRestore
AND convert(varchar,bs.backup_start_date,120) >= @PITDateTime
ORDER BY 3
)
SELECT
restore_cmd + end_point restore_cmd
FROM
(
-- Grab the tlog backups.
SELECT
bs.backup_start_date,
'RESTORE LOG RCPProd FROM DISK = ''' + bmf.physical_device_name + ''' WITH ' restore_cmd,
-- Break the LSN chain when a previous restore is detected
bs.checkpoint_lsn - LAG(bs.checkpoint_lsn,1,0) OVER (ORDER BY backup_start_date) incr_checkpoint_lsn,
CASE WHEN LEAD(bs.checkpoint_lsn,1,0) OVER (ORDER BY backup_start_date) < bs.checkpoint_lsn
THEN 'STOPAT = N''' + @PITDateTime +'''' ELSE 'NORECOVERY' END end_point
FROM
cte_lsn cl
INNER JOIN msdb.dbo.backupset bs ON cl.database_backup_lsn = bs.database_backup_lsn
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE
bs.last_lsn <= cl.last_lsn
AND bs.is_copy_only = 0
UNION ALL
-- Grab the full backup.
SELECT
bs.backup_start_date,
'RESTORE DATABASE ' + @DBToRestore + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY',
1, -- incr_checkpoint_lns placeholder
'' -- end_point placeholder
FROM
cte_lsn cl
INNER JOIN msdb.dbo.backupset bs ON cl.database_backup_lsn = bs.checkpoint_lsn
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE
bs.last_lsn <= cl.last_lsn
AND bs.is_copy_only = 0
) subset
WHERE incr_checkpoint_lsn > 0
ORDER BY backup_start_date;
END
Hoping that someone can shed some light on the proper way to find this info...
Thanks!
Rich
August 27, 2019 at 9:31 am
HI,
The script restores all logs after the FULL backup. How do you determine at which point in the last log file you need to restore to and what is the interval between Log backups.
August 27, 2019 at 12:27 pm
Generally: the log files first_lsn needs to be <= the full backups last_lsn and the log files last_lsn needs to be greater than the full backups last_lsn. When this condition is true that is the first restorable log file. You can get this data from the metadata of the backup files. Use can use command RESTORE HEADER ONLY to get the metadata.
August 27, 2019 at 6:17 pm
I am slightly confused. Are you doing restores to the same point in time each time you do a restore? Also, what are you attempting to achieve with these restores?
August 29, 2019 at 1:07 pm
Super Cat wrote:The script restores all logs after the FULL backup. How do you determine at which point in the last log file you need to restore to and what is the interval between Log backups.
The restore point is narrowed down by the application user, e.g. "I think I need to restore it back to around 3:45 PM". From there, I use a tool to inspect the transaction logs taken around then to determine an exact time to restore.
The log backups run every 15 minutes to meet our RPO for DR.
Thanks,
Rich
August 29, 2019 at 1:10 pm
I am slightly confused. Are you doing restores to the same point in time each time you do a restore? Also, what are you attempting to achieve with these restores?
In this case, yes, the restores were done to the same point-in-time. The issue is that in between those restores, there was another full backup and subsequent transaction log backups, which is what I think confused SSMS into thinking the LSN chain was broken.
Thanks,
Rich
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply