Automated Database Restore Script Out

2014-12-08

Limitation:

In order to be compatible with SQL 2005 ( no default value for T-SQL variable), I changed the code script method.

A lot of posts that used “RESTORE FILELISTONLY” and “BackupSet” table to grab backup files.

Yes, I agree it would be more accurate for latest backup and correct LSN/DB logical name. But if someone drop/detach DB and left backup histories and backup files. It would be incorrect. Additional, sometimes when you use “RESTORE FILELISTONLY” to grab backup files, it will show “Access Denied” message.

In order to avoid the problem, I use “sys.Master_files” table to match “Backupset” and “BackupsetMediafamily”.

The T-SQL Script’s purposes:

  • For current online Database
  • For moving or replacing (overwriting) a database to another Instance or a new location
  • Script out only, you need to execute it by yourself manually

    Parameters:

    @NEWRESTOREDBPATH : Where the new DB *.mdf/*.ndf location will be (Do not put “\” suffix)

    @NEWRESTORELOGPATH : Where the new DB *.ldf location will be (Do not put “\” suffix)

    @NEWBKCOPYPATH : If you copy all *.Bak to the new location B. (Sometime you need to copy *.bak to your local drives to accelerate backup speed or prevent Network package loss while you r restoring)

    @ExcludeDbs : Default we don’t need to restore System DB, because System DB restoring is different from User Database

    @RestoreDbs: Default is to script out all DB, or you can indicate which Database you want to script out, use the same format as @ExcludeDbs

    Credits to:

    Praveen Rayan D'sa : Fully automated SQL Server Restore Script
    http://social.technet.microsoft.com/wiki/contents/articles/23800.fully-automated-sql-server-restore-script.aspx

    Hennis : Updated: create point-in-time restore script automatically
    http://thefirstsql.com/2012/05/29/updated-create-point-in-time-restore-script-automatically/

    Vinoth Nm: PowerShell Script to Find last Backup and Restore of Databases
    http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/03/02/powershell-script-to-find-last-backup-and-restore-of-databases.aspx

    Manish Kaushik: PowerShell script to backup a database and restore on other server.
    http://bi-bigdata.com/2012/12/16/power-shell-script-to-backup-a-database-and-restore-on-other-server/

    Steve Wiseman: DPM 2010 Powershell script to recover SQL database
    http://www.networksteve.com/forum/topic.php/DPM_2010_Powershell_script_to_recover_SQL_database/?TopicId=39512&Posts=0

    The script is below:

  • --FOR SQL 2005, Not Support Default Value for Variables, no TDE DB info
    SET NOCOUNT ON
     
    --SQL Server from where you have taken backups and need to be restored.
    DECLARE -- Databases do not wish to restore
        @ExcludeDbs VARCHAR(255),
        --  Optional, by default restores all databases other than those mentioned in ExcludeDbs list, can mention any particular database needs to be restored like '(''Database1'', ''Database2'')'
        @RestoreDbs VARCHAR(255)
    DECLARE @dbname VARCHAR(100),
        @physicalpath VARCHAR(500),
        @BackupDate DATETIME,
        @cmd NVARCHAR(max),
        @logicalname VARCHAR(255),
        @PhysicalFileName VARCHAR(max),
        @type VARCHAR(5),
        @NEWRestoreDBPath VARCHAR(500),
        @NEWRestoreLOGPath VARCHAR(500),
        @NEWBKCOPYPATH VARCHAR(500),
        @firstlsn NUMERIC(25, 0)
    DECLARE @FULLBKNAME VARCHAR(256),
        @DiffBKPathName VARCHAR(256),
        @LogBKPathName VARCHAR(256),
        @DiffBKName VARCHAR(256),
        @LogBKName VARCHAR(256),
        @Diff_BackupStartDate DATETIME,
        @Log_BackupStartDate DATETIME,
        @SQL VARCHAR(max)
     
    SET @SQL = Null
    SET @NEWRESTOREDBPATH = 'S:\SQLData';
    SET @NEWRESTORELOGPATH = 'L:\SQLLog';
    SET @NEWBKCOPYPATH = 'S:\COPY';
    SET @ExcludeDbs = '(''master'' , ''model'' , ''msdb'')'
    SET @RestoreDbs = NULL
     
    IF Object_id('tempdb..#RestoreScriptResult') IS NOT NULL
        DROP TABLE #RestoreScriptResult;
    CREATE TABLE #RestoreScriptResult ([RestoreScript] NVARCHAR(max));
     
    --A First got the lastest backup date and name from FULL bk
    SET @cmd = 'DECLARE restore_db Cursor For ' + CHAR(10) + 'SELECT a.database_name, BackupDate, physical_device_name,first_lsn FROM msdb.dbo.backupset A ' + CHAR(10) + ' INNER JOIN (SELECT database_name, BackupDate = MAX(backup_start_date) FROM msdb.dbo.backupset WHERE type = ''D'' '
     
    IF @RestoreDbs IS NULL
        SET @cmd = @cmd + ' AND database_name NOT IN ' + @ExcludeDbs + CHAR(10)
    ELSE
        SET @cmd = @cmd + ' AND database_name IN ' + @RestoreDbs
    SET @cmd = @cmd + ' GROUP BY database_name) as b ' + CHAR(10) + ' ON A.database_name = b.database_name and a.backup_start_date = BackupDate ' + CHAR(10) + ' INNER JOIN msdb.dbo.backupmediafamily C ON C.media_set_id = A.media_set_id ' + CHAR(10) +
    'WHERE a.database_name in (SELECT DISTINCT DB_NAME(database_Id) FROM sys.master_files)' + CHAR(10) +  'ORDER BY database_name '
    EXEC sp_executesql @cmd
     
    OPEN restore_db
    FETCH NEXT
    FROM restore_db
    INTO @dbname,@BackupDate,@physicalpath,@firstlsn
     
    WHILE @@FETCH_STATUS = 0
    BEGIN --ROOT BEGIN
        --Get DB Backup Name
        SET @FULLBKNAME = Substring(@physicalpath, LEN(@physicalpath) - CHARINDEX('\', REVERSE(@physicalpath)) + 2, CHARINDEX('\', REVERSE(@physicalpath)))
        
        SET @cmd = 'RESTORE DATABASE [' + @dbname + ']  FROM DISK = ''' + @NEWBKCOPYPATH + '\' + @FULLBKNAME + ''' WITH NORECOVERY ,   ' + CHAR(10)
                
                DECLARE file_list CURSOR
                FOR    
                    SELECT [name],    [Physical_Name],[Type] FROM sys.master_files
                    WHERE DB_NAME([database_id]) = @dbname
                    ORDER BY type, data_space_id, file_id
                OPEN file_list
                FETCH NEXT
                FROM file_list
                INTO @LogicalName,@PhysicalFileName,@type
     
                WHILE @@fetch_status = 0
                BEGIN --B0
                    -- If it is data file move to data file location.
                    IF @type = 0
                        SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @NEWRESTOREDBPATH + '\' + Substring(@PhysicalFileName, LEN(@PhysicalFileName) - CHARINDEX('\', REVERSE(@PhysicalFileName)) + 2, CHARINDEX('\', REVERSE(@PhysicalFileName))) + ''',' + CHAR(10)
                    ELSE
                        -- Log files move to log file location.
                        SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @NEWRESTORELOGPATH + '\' + Substring(@PhysicalFileName, LEN(@PhysicalFileName) - CHARINDEX('\', REVERSE(@PhysicalFileName)) + 2, CHARINDEX('\', REVERSE(@PhysicalFileName))) + '''' + CHAR(10)
     
                    FETCH NEXT
                    FROM file_list
                    INTO @LogicalName,@PhysicalFileName,@type
                END --B0 END
                CLOSE file_list
                DEALLOCATE file_list
                
                SET @cmd = @cmd + 'GO --00' + CHAR(13)
     
    --Get Diff Backup name and Backup Date
    --Diff and Log database_backup_lsn rely on Full Backup's First_Lsn
    SELECT TOP 1 @DiffBKPathName = physical_device_name, @Diff_BackupStartDate = backup_start_date
            FROM msdb.dbo.backupset A
            INNER JOIN msdb.dbo.backupmediafamily C ON C.media_set_id = A.media_set_id
            WHERE  [type] = 'I'
                AND a.database_backup_lsn = @firstlsn
                AND a.database_name = @dbname
                AND backup_start_date >= @BackupDate
            ORDER BY backup_start_date DESC;
     
    IF @DiffBKPathName IS NOT NULL
            BEGIN --B1
                SET @DiffBKName = Substring(@DiffBKPathName, LEN(@DiffBKPathName) - CHARINDEX('\', REVERSE(@DiffBKPathName)) + 2, CHARINDEX('\', REVERSE(@DiffBKPathName)));
                PRINT @DiffBKPathName;
                SET @SQL = 'RESTORE DATABASE [' + @dbname + ']  FROM DISK = ''' + @NEWBKCOPYPATH + '\' + @DiffBKName + ''' WITH NORECOVERY ,   ' + CHAR(10) + 'GO --11' + CHAR(13)
     
    --Combine Log backup Script
                DECLARE file_list2 CURSOR
                FOR
                        SELECT [physical_device_name] FROM msdb.dbo.backupset a
                        INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
                        WHERE a.database_name = @dbname
                            AND a.database_backup_lsn = @firstlsn
                            AND a.backup_start_date >= @Diff_BackupStartDate
                            AND a.type = 'L'
                        ORDER BY a.backup_start_date, a.first_lsn
                OPEN file_list2
                FETCH NEXT
                FROM file_list2
                
                INTO @PhysicalFileName
                WHILE @@fetch_status = 0
                    BEGIN --B2
                            SET @SQL = @SQL + ' RESTORE LOG [' + @dbname + ']  FROM DISK = ''' + @NEWBKCOPYPATH + '\' + Substring(@PhysicalFileName, LEN(@PhysicalFileName) - CHARINDEX('\', REVERSE(@PhysicalFileName)) + 2, CHARINDEX('\', REVERSE(@PhysicalFileName))) + ''' WITH NORECOVERY' + CHAR(10)
                    FETCH NEXT
                    FROM file_list2
                    INTO @PhysicalFileName
                    END     --B2 END
                CLOSE file_list2
                DEALLOCATE file_list2
            
            SET @CMD = @CMD + @SQL + 'GO --22' + char(13)
            END --B1 END
     
    ELSE 
                BEGIN --BB
                        SET @Diff_BackupStartDate = @BackupDate
                    
                        --Check if Log bk exist
                        SELECT TOP 1 @LogBKPathName = [physical_device_name] FROM msdb.dbo.backupset a
                                    INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
                                    WHERE a.database_name = @dbname
                                        AND a.database_backup_lsn = @firstlsn
                                        AND a.backup_start_date >= @Diff_BackupStartDate
                                        AND a.type = 'L'
                        ORDER BY a.backup_start_date, a.first_lsn
        
                    IF @LogBKPathName IS NOT NULL
                        BEGIN --B4
                            DECLARE file_list3 CURSOR
                            FOR
                                    SELECT [physical_device_name] FROM msdb.dbo.backupset a
                                    INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
                                    WHERE a.database_name = @dbname
                                        AND a.database_backup_lsn = @firstlsn
                                        AND a.backup_start_date >= @Diff_BackupStartDate
                                        AND a.type = 'L'
                                    ORDER BY a.backup_start_date
                            OPEN file_list3
                            FETCH NEXT
                            FROM file_list3
                            
                            INTO @PhysicalFileName
                            WHILE @@fetch_status = 0
                                BEGIN --B3
                                        SET @SQL = @SQL + ' RESTORE LOG [' + @dbname + ']  FROM DISK = ''' + @NEWBKCOPYPATH + '\' + Substring(@PhysicalFileName, LEN(@PhysicalFileName) - CHARINDEX('\', REVERSE(@PhysicalFileName)) + 2, CHARINDEX('\', REVERSE(@PhysicalFileName))) + ''' WITH NORECOVERY' + CHAR(10)
                                FETCH NEXT
                                FROM file_list3
                                INTO  @PhysicalFileName
                                END     --B3 END
                                CLOSE file_list3
                                DEALLOCATE file_list3
                        
                        SET @CMD = @CMD + @SQL + 'GO --33'
                        END --B4 End
                END    --BB END
        
            BEGIN --B5
                    INSERT INTO #RestoreScriptResult
                    SELECT @CMD
            END     --B5 END
     
    FETCH NEXT
    FROM restore_db
    INTO @dbname,@BackupDate,@physicalpath,@firstlsn
     
    END --ROOT BEGIN END
    CLOSE restore_db
    DEALLOCATE restore_db
     
    SELECT *
    FROM #RestoreScriptResult;
     
    DROP TABLE #RestoreScriptResult;
     
     
     
    Newer Older