Restore All Databases Script

Question

I am migrating SQL Server DBs to a new instance.

I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog.

Does anyone know of a sample script to do this?

Thanks!

Answer 1

I, personally, use sp_restorecriptgenie by Paul Brewer. Here's the link to the SCC article that has it. Under 'The Procedure'

http://www.sqlservercentral.com/articles/Restore+database/95839/

Basically, once you build the stored procedure, just run exec sp_restorescriptgenie and the script will go out and get all backups for every DB (even transaction logs) and give you a script to generate them. Also generates the DBCC CHECKDB commands to check the DBs after restoring.

I actually just used this script about an hour ago so I can do a test restore and verify my backups.

Answer 2

there is a very good script on the link below:

How to generate database restore script in SQL Server

Based on that script I have created my stored procedure below, which works very well, although it does not generate the DBCC CHECKDB:

it needs 3 parameters:

@Database sysname - the database name you want to generate the restore
@MovePathLog NVARCHAR(1008) - where to move the transaction log file to
@MovePathData NVARCHAR(1008) - where to move the data file(s) to

Here is the code:

use master
go

IF OBJECT_ID('sp_genRestoreScripts') IS NOT NULL
   DROP PROCEDURE sp_genRestoreScripts
GO

create procedure sp_genRestoreScripts
@Database sysname,
@MovePathLog NVARCHAR(1008),
@MovePathData NVARCHAR(1008)
as begin

DECLARE @FullMediaSetID NVARCHAR(20)
DECLARE @BackupSetID NVARCHAR(20)
DECLARE @FullPath NVARCHAR(400)
DECLARE @FullPosition NVARCHAR(20)

DECLARE @LogMediaSetID NVARCHAR(20)
DECLARE @LogPath NVARCHAR(400)
DECLARE @LogPosition NVARCHAR(20)

DECLARE @DiffMediaSetID NVARCHAR(20)
DECLARE @DiffPath NVARCHAR(400)
DECLARE @DiffPosition NVARCHAR(20)

DECLARE @SQLMove NVARCHAR(MAX)
SET @SQLMove =''

IF ( @MovePathData <> '' OR @MovePathLog <> '')
BEGIN
    DECLARE @LogicalName NVARCHAR(200)
    DECLARE @FileID NVARCHAR(10)
    DECLARE @FileType NVARCHAR(10)
    DECLARE @ExtName NVARCHAR(20)

    DECLARE MoveCur CURSOR FOR 
    SELECT m.name
        ,m.file_id
        ,m.type 
    FROM sys.master_files m 
        INNER JOIN sys.databases d 
            ON m.database_id = d.database_id
    WHERE d.name = @Database

    OPEN MoveCur

    FETCH NEXT FROM MoveCur INTO @LogicalName,@FileID,@FileType

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ExtName = CASE  
                            WHEN @FileID = 1 THEN '.mdf'
                            WHEN @FileID > 1 THEN CASE @FileType 
                                                       WHEN 0 THEN '.ndf' 
                                                       ELSE '.ldf' 
                                                  END
                       END
        SET @SQLMove = @SQLMove + CHAR(10) + '   , MOVE ' + '''' + @LogicalName + '''' 
                       + ' TO ' + '''' + 

                      CASE @FileType 
                           WHEN 0 THEN @MovePathData 
                           ELSE @MovePathLog
                      END

                       + @LogicalName + @ExtName + ''''
        FETCH NEXT FROM MoveCur INTO @LogicalName,@FileID,@FileType     
    END
    CLOSE MoveCur
    DEALLOCATE MoveCur
END


SELECT 
    @BackupSetID = backup_set_id,
    @FullMediaSetID = media_set_id, 
    @FullPosition = position
FROM msdb.dbo.backupset 
WHERE backup_set_id =
(
    SELECT MAX(backup_set_id) 
    FROM msdb.dbo.backupset 
    WHERE database_name = @Database 
        AND type='D'
    GROUP BY database_name
)

SELECT @FullPath = physical_device_name  
FROM msdb.dbo.backupmediafamily 
WHERE media_set_id = @FullMediaSetID

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.backupset 
    WHERE database_name = @Database AND type IN('L','I') AND backup_set_id > @BackupSetID)
BEGIN
    PRINT REPLICATE('-',200)
    PRINT '-----------//Full backup restore only'
    PRINT REPLICATE('-',200)
    PRINT 'RESTORE DATABASE ' + @Database + CHAR(10) 
        + ' FROM DISK = ''' + @FullPath + '''' + CHAR(10) 
        + '   WITH FILE = ' + @FullPosition + ', REPLACE' + @SQLMove
END


IF EXISTS(SELECT 1 FROM msdb.dbo.backupset 
    WHERE database_name = @Database AND type='L' AND backup_set_id > @BackupSetID)
BEGIN
    PRINT REPLICATE('-',200)
    PRINT '-----------//Full backup and log backup restore '
    PRINT REPLICATE('-',200)
    PRINT 'RESTORE DATABASE ' + @Database + CHAR(10)
        + ' FROM DISK = ''' + @FullPath + '''' + CHAR(10)
        + '   WITH FILE = ' + @FullPosition + ', NORECOVERY, REPLACE ' + @SQLMove

    DECLARE log_cursor CURSOR FOR 
    SELECT media_set_id
        ,position 
    FROM msdb.dbo.backupset 
    WHERE database_name = @Database 
        AND type='L' 
        AND backup_set_id > @BackupSetID 
    ORDER BY backup_set_id

    OPEN log_cursor

    FETCH NEXT FROM log_cursor INTO @LogMediaSetID,@LogPosition

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @LogPath = physical_device_name  
        FROM msdb.dbo.backupmediafamily 
        WHERE media_set_id = @LogMediaSetID

        PRINT 'RESTORE Log ' + @Database + CHAR(10) 
            + ' FROM DISK = ''' + @LogPath
            + ''' WITH FILE = ' + @LogPosition + ', NORECOVERY'
        FETCH NEXT FROM log_cursor INTO @LogMediaSetID,@LogPosition
    END
    CLOSE log_cursor
    DEALLOCATE log_cursor

    PRINT 'RESTORE DATABASE ' + @Database + ' WITH RECOVERY '
END


IF EXISTS(SELECT 1 FROM msdb.dbo.backupset 
    WHERE database_name = @Database AND type='I' AND backup_set_id > @BackupSetID)
BEGIN
    PRINT REPLICATE('-',200)
    PRINT '-----------//Full backup and differential backup restore '
    PRINT REPLICATE('-',200)
    PRINT 'RESTORE DATABASE ' + @Database + CHAR(10)  
        + ' FROM DISK = ''' + @FullPath + '''' + CHAR(10)
        + '   WITH FILE = ' + @FullPosition + ', NORECOVERY, REPLACE ' + @SQLMove

    DECLARE diff_cursor CURSOR FOR 
    SELECT media_set_id
        ,position 
    FROM msdb.dbo.backupset 
    WHERE database_name = @Database 
        AND type='I' 
        AND backup_set_id > @BackupSetID 
    ORDER BY backup_set_id

    OPEN diff_cursor

    FETCH NEXT FROM diff_cursor INTO @DiffMediaSetID,@DiffPosition

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @DiffPath = physical_device_name  
        FROM msdb.dbo.backupmediafamily 
        WHERE media_set_id = @DiffMediaSetID

        PRINT 'RESTORE DATABASE ' + @Database + CHAR(10)
            + ' FROM DISK = ''' + @DiffPath 
            + ''' WITH FILE = ' + @DiffPosition + ', NORECOVERY'

        FETCH NEXT FROM diff_cursor INTO @DiffMediaSetID,@DiffPosition
    END
    CLOSE diff_cursor
    DEALLOCATE diff_cursor

    PRINT 'RESTORE DATABASE ' + @Database + ' WITH RECOVERY '
END
end 
GO

This is how you run the procedure for a single database:

use master
go
exec sp_genRestoreScripts
        @Database ='CAAltosextracts',
        @MovePathLog ='F:\logs\',
        @MovePathData ='e:\DATA\'

and for multi-databases I use the sp_foreachdb stored procedure

     declare @db_list NVARCHAR(MAX)


     SELECT @db_list  = STUFF((
                                SELECT ', ' + name FROM sys.databases
                                WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB')
                                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

     --exec sp_foreachdb @database_list = @db_list
              --       ,@command='use ?; print db_name() + char(13)'   

     exec sp_foreachdb @database_list = @db_list
                     ,@command='
                                 exec sp_genRestoreScripts
                                        @Database =[?],
                                        @MovePathLog =''F:\logs\'',
                                        @MovePathData =''E:\DATA\'' 
                               ' 

Answer 3

I am migrating SQL Server DBs to a new instance.

You can use dbatools - Copy-DbaDatabase to do backup/restore from one source to multiple destinations.

Also, for restore dbatools - Restore-DbaDatabase - allows you to just point to a backup location or Trust your msdb backup history to build commands and allow you to do a point in time restore as well. It automatically figures out full, diff and tlogs.

You can even generate your restore scripts using Export-DbaScript to a file.

Note: I have used both dbatools and sp_restoreGene - both are really good. The plus point of dbatools is that you can use other cmdlets for your entire server management.