Long time ago, i came up with following script to restore database automatic but we have always ONE BACKUP FILE.
now, i am thinking to do something different, what if we have 3 or 4 (Multiple backup files for ABC Database). how can i change my script so it will recognize all backup file once and give me restore statement. please help!
DECLARE @dbName VARCHAR(40)
DECLARE @destination_database_files TABLE
(
dbname VARCHAR(100) ,
databaseid INT ,
name VARCHAR(100) ,
destfilename VARCHAR(200) NULL
);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @cmd NVARCHAR(500);
DECLARE @fileList TABLE
(
backupFile NVARCHAR(255)
);
DECLARE @lastFullBackup NVARCHAR(500);
DECLARE @lastDiffBackup NVARCHAR(500);
DECLARE @backupFile NVARCHAR(500);
DECLARE @OriginalBackupFile NVARCHAR(500);
DECLARE @backupPath VARCHAR(100);
SELECT TOP 1
@OriginalBackupFile = LEFT(BMF.physical_device_name,
LEN(BMF.physical_device_name)
- CHARINDEX('\',
REVERSE(BMF.physical_device_name)))
FROM msdb.dbo.backupmediafamily BMF
JOIN msdb.dbo.backupmediaset BMS ON BMF.media_set_id = BMS.media_set_id
JOIN msdb.dbo.backupset BS ON BS.media_set_id = BMS.media_set_id
WHERE BS.database_name = @dbName -- SELECT DB_NAME()
ORDER BY BS.backup_finish_date DESC;
SET @OriginalBackupFile = @OriginalBackupFile + '\';
SET @cmd = 'DIR /b ' + @OriginalBackupFile;
INSERT INTO @fileList
( backupFile )
EXEC master.sys.xp_cmdshell @cmd;
-- 1 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%__FULL%';
PRINT @lastFullBackup;
INSERT INTO @destination_database_files
SELECT sd.name ,
sd.dbid ,
sm.name ,
sm.physical_name
FROM sys.sysdatabases sd
JOIN sys.master_files sm ON sd.dbid = sm.database_id
WHERE sd.name = @dbName;
SET @SQL = N'RESTORE database ' + @dbName + ' from disk = '''
+ @OriginalBackupFile + @lastFullBackup
+ ''' with RECOVERY, replace, stats,';
SELECT @SQL = @SQL + N' move ''' + name + ''' to ''' + destfilename
+ ''','
FROM @destination_database_files;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1);
-- remove last ','
PRINT @SQL;
