Automated Database Restore Script Out

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;