Restore most rececent backup sequence(FULL/DIFF/LOG)

/*
Hope this help someone in a pinch. This "SHOULD" generate
a valid restore script inclusive of your FULL, DIFF*, and LOG(S)
*=I didn't take into account multiples of this
Restore most recent backup sequence(FULL/DIFF/LOG)
*/
SET NOCOUNT ON
DECLARE
@DBName NVARCHAR(128)
,@dSQL VARCHAR(MAX)
,@restoreFULL VARCHAR(MAX)
,@restoreDIFF VARCHAR(MAX)
,@restoreLOG VARCHAR(MAX)
,@MIH VarChar(MAX) --// MAKE IT HAPPEN
,@restoreType Varchar(25) --// NEW | RESTORE

SELECT @DBName = 'ansi', @restoreType = '~NEW'

DECLARE @recent TABLE (
Database_Name NVARCHAR(128)
, FIRST_FULL_LSN NUMERIC(25,0)
, FIRST_FULL_DATE DATETIME
, FIRST_FULL_BACKUP_SET_ID INT
, FIRST_DIFF_DATE DATETIME
, FIRST_DIFF_BACKUP_SET_ID INT
, FIRST_LOG_DATE DATETIME
, FIRST_LOG_BACKUP_SET_ID INT
)
INSERT INTO @recent
SELECT TOP 1
D.Database_Name
,D.first_lsn
,D.backup_finish_date
,D.backup_set_id
,I.backup_finish_date
,I.backup_set_id
,L.backup_finish_date
,L.backup_set_id
FROM
MSDB.dbo.BackupSet D (NOLOCK)
LEFT JOIN MSDB.dbo.BackupSet I (NOLOCK) ON I.Type = 'I' AND I.database_backup_lsn = D.checkpoint_lsn
LEFT JOIN MSDB.dbo.BackupSet L (NOLOCK) ON L.Type = 'L' AND L.database_backup_lsn = D.checkpoint_lsn AND CASE WHEN I.backup_finish_date > L.backup_finish_date THEN 0 ELSE 1 END = 1
WHERE
D.Database_Name = @DBName
AND D.Type = 'D'
GROUP BY
D.Database_Name
,D.first_lsn
,D.backup_finish_date
,D.backup_set_id
,I.backup_finish_date
,I.backup_set_id
,L.backup_finish_date
,L.backup_set_id
ORDER BY
D.backup_finish_date DESC
, D.backup_set_id ASC
, I.backup_finish_date ASC
, I.backup_set_id ASC
, L.backup_finish_date ASC
, L.backup_set_id ASC
IF object_id('tempdb..#fileinfo') IS NOT NULL drop table #fileinfo

create table #fileinfo (
[db] varchar(100),
name varchar(100),
filename varchar(100),
FileType varchar (100))

SELECT @dSQL= 'INSERT INTO #fileinfo([db],name,filename, FileType)
SELECT DISTINCT ''[' + @DBName + ']'',rtrim(name),rtrim(filename), CASE WHEN status & 0x40 = 0x40 THEN ''DATA'' ELSE ''LOG'' END from [' + @DBName + ']..sysfiles'

EXEC(@dSQL)

--SELECT * FROM #fileinfo

SELECT @restoreFULL = 'RESTORE DATABASE [' + @DBName + ']
FROM '
+
(SELECT
CASE WHEN family_sequence_number > 1 THEN ' ,' ELSE '
' END +
'DISK = ''' + bmf.physical_device_name + '''
'
FROM
msdb..backupset bs (NOLOCK)
JOIN msdb..backupmediafamily bmf (NOLOCK) ON bs.media_set_id = bmf.media_set_id
JOIN @recent R ON R.FIRST_FULL_BACKUP_SET_ID = bs.backup_set_id

ORDER BY
bs.backup_set_id DESC
,bs.media_set_id DESC
,bmf.family_sequence_number asc
FOR XML PATH('')) +
'WITH ' +
CASE WHEN @restoreType = 'NEW' THEN (
SELECT '
' + CASE WHEN ROW_NUMBER() OVER (ORDER BY db) > 1 THEN ' ,' ELSE ' ' END + 'MOVE [' + name + '] TO ''' + filename + '''
,'
FROM
#fileinfo
FOR XML PATH('')) ELSE '
' END + 'NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10'
SELECT @restoreFULL = REPLACE( REPLACE(@restoreFULL,'
',''),'&','&')

SELECT @restoreDIFF = 'RESTORE DATABASE [' + @DBName + ']
FROM '
+
(SELECT
CASE WHEN family_sequence_number > 1 THEN ' ,' ELSE '
' END +
'DISK = ''' + bmf.physical_device_name + '''
'
FROM
msdb..backupset bs (NOLOCK)
JOIN msdb..backupmediafamily bmf (NOLOCK) ON bs.media_set_id = bmf.media_set_id
JOIN @recent R ON R.FIRST_DIFF_BACKUP_SET_ID = bs.backup_set_id

ORDER BY
bs.backup_set_id DESC
,bs.media_set_id DESC
,bmf.family_sequence_number asc
FOR XML PATH('')) +
'WITH NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10'
SELECT @restoreDIFF = REPLACE( REPLACE(@restoreDIFF,'
',''),'&','&')

SELECT @restoreLOG = 'RESTORE DATABASE [' + @DBName + ']
FROM '
+
(SELECT
CASE WHEN family_sequence_number > 1 THEN ' ,' ELSE '
' END +
'DISK = ''' + bmf.physical_device_name + '''
'
FROM
msdb..backupset bs (NOLOCK)
JOIN msdb..backupmediafamily bmf (NOLOCK) ON bs.media_set_id = bmf.media_set_id
JOIN @recent R ON R.FIRST_LOG_BACKUP_SET_ID = bs.backup_set_id

ORDER BY
bs.backup_set_id DESC
,bs.media_set_id DESC
,bmf.family_sequence_number asc
FOR XML PATH('')) +
'WITH NORECOVERY, MAXTRANSFERSIZE = 1048576,STATS=10'
SELECT @restoreLOG = REPLACE( REPLACE(@restoreLOG,'
',''),'&','&')

SELECT @MIH =
@restoreFULL
+ @restoreDIFF
+ @restoreLOG
+ '
RESTORE DATABASE [' + @DBName + '] WITH RECOVERY'

PRINT @MIH
SET NOCOUNT OFF

Comments :

0 comments to “Restore most rececent backup sequence(FULL/DIFF/LOG)”