vwRestoreHistory

This JOINs the three available tables from msdb that have information on restore history.  One row per file.  If the database has one data and one log file, there will be two rows for this restore.

Use Admin

go

IF OBJECT_ID('dbo.vwRestoreHistory') Is Not Null

       DROP VIEW dbo.vwRestoreHistory

go

CREATE VIEW dbo.vwRestoreHistory

AS

/*     DATE          AUTHOR        REMARKS

       1/25/18       PPaiva        Initial creation.

       SELECT *

       FROM vwRestoreHistory

       ORDER BY ID desc

*/

SELECT  h.restore_history_id ID,

              restore_date RestoreDate,

              destination_database_name DestDB,

              fg.filegroup_name FileGroupName,

              f.destination_phys_name DestPathFile,

              user_name,

              backup_set_id,

              restore_type,

              replace,

              recovery,

              restart,

              stop_at,

              device_count,

              stop_at_mark_name,

              stop_before,

              h.restore_history_id

FROM msdb.dbo.restorehistory h

JOIN msdb.dbo.restorefilegroup fg

       ON fg.restore_history_id = h.restore_history_id

JOIN msdb.dbo.restorefile f

       ON f.restore_history_id = h.restore_history_id

Scripts for Backup and Restore

Someone posed this question:  How do you generate scripts for backing up all the databases on ServerA and restoring them on ServerB?

BACKING UP

Generating the backup script is easy:

SELECT 'BACKUP DATABASE [' + Name + '] TO DISK = ''D:\Backup\' + name + '.bak'''

FROM sys.databases

ORDER BY name

Or, refer to a post from December 2011 which does this in a slightly fancier fashion via a stored proc that gives options for putting the date or date/time in the backup filename.  The stored proc is called BackupDB.

Note the proc BackupDB has a couple of dependencies, which can be easily installed:

        udfAddBackslashIfNec        

        DoesPathExist

        

RESTORING

Restoring is trickier, but doable.   I recommend taking the time to write the script – it will help you for years to come.

The RESTORE DATABASE script is shown in this stored procedure, RestoreScriptGenerateFromBak.

RestoreScriptGenerateFromBak

The syntax for restoring a database file is simple if you have the same paths for the file locations between the source and destination servers. 

However if the database file locations in the source and destination servers are not the same, the RESTORE DATABASE syntax will have to include WITH MOVE which requires that you know the logical name of each file.

The logical name can be retrieved with this command.  Note it will also indicate whether the file is a database or log file.

RESTORE FILELISTONLY

FROM Disk = 'E:\Backups\CCS.bak'

Here’s a stored procedure which will generate the RESTORE DATABASE script when you pass in the location of the backup file.

It has one dependency which is easy to install:

        udfGetFilenameOnly

This script is especially handy when you have a large number of data or log files underlying your database.  The script allows you to pass in a path for Data and Log files.  If you need them to be on different paths, simply change them manually after the script is generated.

After running the proc, the script is shown on the Messages pane.  

Use Admin

GO

CREATE PROC dbo.RestoreScriptGenerateFromBak

      @PathFilename varchar(300),

      @NewDBName varchar(100),

      @DataPath varchar(200),

      @LogPath varchar(200)

AS

/*    DATE        AUTHOR            REMARKS

      2/5/12            PPaiva            Initial creation.

     

      DESCRIPTION

            Creates a restore script for a given backup file

                  with a common specified destination folder for data

                  files, and another for log files.

            This is useful when making a dev copy of a multi-file

                  production database.

     

      DEPENDENCIES

            Admin.dbo.udfGetFilenameOnly()

            RESTORE FILELISTONLY

     

      USAGE

            Exec RestoreScriptGenerateFromBak

                  'D:\Backup\ccs.bak',

                  'CCS',

                  'D:\',

                  'C:\Paul\Sql'

            Exec RestoreScriptGenerateFromBak

                  'K:\Backups\Prod01\MyDB_2012-02-03.bak',

                  'MyDB',

                  'M:\Data',

                  'L:\Logs'

      DEBUG

            RESTORE FILELISTONLY

            FROM DISK = 'E:\Sql\Backup\MyDB_2012-02-03.bak '

           

*/

SET NOCOUNT ON

DECLARE @Sql varchar(Max),

            @crlf varchar(2),

            @Path varchar(200)

           

SET @crlf = CHAR(13) + CHAR(10)

SET @DataPath = dbo.udfAddBackslashIfNec(@DataPath)

SET @LogPath = dbo.udfAddBackslashIfNec(@LogPath)

CREATE TABLE #FileList(

      LogicalName nvarchar(128) NOT NULL PRIMARY KEY,

      PhysicalName nvarchar(260),

      Type char(1),

      FileGroupName nvarchar(128),

      Size numeric(20,0),

      MaxSize numeric(20,0),

      FileID bigint,

      CreateLSN numeric(22,0),

      DropLSN numeric(25,0),

      UniqueID uniqueidentifier,

      ReadOnlyLSN numeric(25,0),

      ReadWriteLSN numeric(25,0),

      BackupSizeInBytes bigint,

      SourceBlockSize int,

      FileGroupID int,

      LogGroupGUID uniqueidentifier,

      DifferentialBaseLSN numeric(25,0),

      DifferentialBaseGUID uniqueidentifier,

      IsReadOnly bit,

      IsPresent bit,

      TDEThumbprint varbinary(32)

      )

-- Determine version of SQL

DECLARE @Version varchar(10) 

SELECT @Version = Convert(varchar(10), ServerProperty('ProductVersion'))

IF Left(@Version, 2) = '9.'

      ALTER TABLE #FileList DROP COLUMN TDEThumbprint

     

SET @Sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @PathFilename + ''''

     

INSERT INTO #FileList

      Exec (@Sql)

SELECT *

FROM #FileList

ORDER BY FileID

DECLARE @LogicalName nvarchar(128),

            @Filename varchar(300),

            @FileID int,

            @MaxFileID int,

            @Type char(1),

            @NumIterations int

SELECT  @MaxFileID = MAX(FileID),

            @FileID = 0,

            @NumIterations = 0,

            @Sql = 'RESTORE DATABASE [' + @NewDBName + ']

FROM DISK = ''' + @PathFilename + '''

WITH '

FROM #FileList

WHILE @FileID < @MaxFileID

      BEGIN

            SET @NumIterations = @NumIterations + 1

     

            -- Get next file

            SELECT @FileID = MIN(FileID)

            FROM #FileList

            WHERE FileID > @FileID

     

            -- Get other attribs for this FileID

            SELECT  @LogicalName = LogicalName,

                        @Filename = dbo.udfGetFilenameOnly(PhysicalName),

                        @Type = Type

            FROM #FileList

            WHERE FileID = @FileID

     

            IF @NumIterations = 1

                  SET @Sql = @Sql

            ELSE

                  SET @Sql = @Sql + '     '

     

            --SELECT @Type Type, @DataPath DataPath, @LogPath LogPath

                 

            IF @Type = 'D'

                  SET @Path = @DataPath

            ELSE

                  BEGIN

                        SET @Path = @LogPath

           

                        -- Force suffix to be .ldf if a log file

                        SET @Filename = REPLACE(@Filename, '.ndf', '.ldf')

                        SET @Filename = REPLACE(@Filename, '.mdf', '.ldf')

                  END

           

            SET @Sql = @Sql + 'MOVE ''' + @LogicalName + ''' TO ''' +  @Path + @NewDBName + '_' + @Filename + ''',' + @crlf

           

     

      END

-- Remove last comma

SET @Sql = LEFT(@Sql, len(@Sql) - 3)

Print @Sql