locked
Automatic Restore multiple backup files for one database RRS feed

  • Question

  • 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;

    Thursday, May 26, 2016 2:13 AM