Josep,
A Very nice script. Very useful.
I've added 2 features:
a) to have a MOVE xx to yy clause, so that the files can be restored to different drives/directories.
b) Stats=1, so that I can see progress.
Best regards
Henrik Staun Poulsen
/******************************************************************
from
http://www.sqlservercentral.com/scripts/Restore/61810/
This script creates the script to restore your database with the information existing in [msdb] database.
It helps you by finding the last FULL backup, the last DIFFERENTIAL backup and all the TRANSACTION LOG backups needed.
It's quite comfortable when you are doing so many differential or log backups.
I hope you enjoy it!!!
Script done by Josep Martínez based on the script done by [jtshyman] named "List SQL backups"
Of course, there's no warranty, etc ...
The variable @DBName should be set to the name of the database you want to query on.
It is not case sensitive unless your collation is.
The variable @Days should be set to how many days back in the records you want to list backups for. By default set to 14 (old enought I think)
*/
-- Important because we're going to 'print' the sql code for the restore
SET NOCOUNT ON
DECLARE @DBName sysname
DECLARE @Days INT, @WithMove INT, @WithStats INT, @Move VARCHAR(MAX), @stats VARCHAR(MAX)
-- These are the only parameters that needs to be configured
SET @DBName='mydatabase'
SET @Days=14-- I think that's old enough
SET @WithMove = 1 -- 1 or 0; 1=include a "move xx to yy" statement. "1" requires that dbname is the current database
SET @WithStats = 1 -- 1 or 0; 1=include a "STATS=1" statement
SET @Move=''
IF @WithMove = 1 BEGIN
SELECT @Move = @Move + 'MOVE ''' + NAME + ''' TO ''' + Physical_Name + ''', ' + CHAR(13)
FROM sys.database_files
IF LEN(@Move) > 2 SELECT @Move = LEFT(@Move, LEN(@Move)-2)
END
SET @stats=''
IF @WithStats=1 BEGIN
SELECT @stats = 'STATS=1,'
END
CREATE TABLE #BackupsHistory
(
id INT IDENTITY(1,1),
backup_start_date DATETIME,
backup_type CHAR(1),
physical_device_name VARCHAR(2000)
)
INSERT INTO #BackupsHistory (backup_start_date,backup_type, physical_device_name)
SELECT S.backup_start_date,
S.type,
M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = @DBName
AND DATEDIFF(DAY,S.backup_start_date,GETDATE()) < @Days
ORDER by backup_start_date
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
-- We get the last Full backup done. That where we are going to start the restore process
SET @lastFullBackup = (SELECT TOP 1 id FROM #BackupsHistory WHERE backup_type='D' ORDER BY backup_start_date DESC)
SET @lastFullBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@lastFullBackup)
-- Restoring the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastFullBackupPath + ''''
PRINT 'WITH '
IF @WithMove =1 BEGIN
PRINT @Move
END
IF @WithStats=1 BEGIN
PRINT @stats
END
-- IF it's there's no backup (differential or log) after it, we set to recovery
IF (@lastFullBackup = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY'
ELSE PRINT 'NORECOVERY'
PRINT 'GO'
PRINT ''
-- We get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup = (SELECT TOP 1 id FROM #BackupsHistory WHERE backup_type='I' AND id>@lastFullBackup ORDER BY backup_start_date DESC)
SET @lastDifferentialBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@lastDifferentialBackup)
-- IF there's a differential backup done after the full backup we script it
IF (@lastDifferentialBackup IS NOT NULL)
BEGIN
-- Restoring the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastDifferentialBackupPath + ''''
PRINT 'WITH '
IF @WithStats=1 BEGIN
PRINT @stats
END
-- IF it's there's no backup (differential or log) after it, we set to recovery
IF (@lastDifferentialBackup = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY'
ELSE PRINT 'NORECOVERY'
PRINT 'GO'
PRINT ''
END
-- For TRANSACTION LOGs
DECLARE @i INT, @logBackupPath VARCHAR(2000)
IF (@lastDifferentialBackup IS NULL)
SET @i = @lastFullBackup + 1
ELSE SET @i = @lastDifferentialBackup + 1
-- Here whe are scripting the restores for the necessary logs
WHILE (@i <= (SELECT MAX(id) FROM #BackupsHistory))
BEGIN
SET @logBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@i)
PRINT 'RESTORE LOG ' + @DBName
PRINT 'FROM DISK=''' + @logBackupPath + ''''
PRINT 'WITH '
IF @WithStats=1 BEGIN
PRINT @stats
END
-- IF it's the last transaction log, we'll say it to recover
IF (@i = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY'
ELSE PRINT 'NORECOVERY'
PRINT 'GO'
PRINT ''
SET @i = @i + 1
END
DROP TABLE #BackupsHistory