Pythian Group

Have you ever been asked to restore a database and wondered which backup files were available? And if many were available, which ones you should use, and in which order?

Getting familiar with the msdb schema, especially the backupset and backupmediafamily tables, helps to answer that question since all the backup history is stored in that database. But you would still have to check if the backups are still available on disk (tape?) and figure out what are the commands to restore, and the order in which to restore each file.

Okay, this is not a difficult thing to do, but when you do it over and over again, it becomes tedious, and the automation bug in you starts to look for a better and quicker way to handle it. The bug in me found the following answer.

An easier way

The stored procedure below may help you to save some time when restoring database backups. I decided not to use the information stored in msdb because there’s no guarantee the information there is still current.

Instead, the procedure takes a backup directory as a parameter and processes the contents of it. The procedure reads the header of all the backup files in the directory and find the optimal set of backup files needed to restore the latest version of the database. You have options to change the database name, database location, and decide if you only want to generate the restore script or actually perform the backup.

The following parameters are available:

Requirements

For this stored procedure to be used xp_cmdshell must be enabled.

Code

use [DBAdmin]
if object_id('dbo.usp_dba_RestoreDatabase') is not null
  drop procedure usp_dba_RestoreDatabase
go
--------------------------------------------------------------
-- Procedure: usp_dba_RestoreDatabase
-- Restores a database from a folder containing backup files
-- for a database.
--
-- Tested on SQL Server 2000 and 2005.
--
-- The author is not responsible for any damages caused by
-- this stored procedure. Use at you own risk.
--
-- Documentation: http://www.pythian.com/news/1515/easier-database-restores
--------------------------------------------------------------
create procedure dbo.usp_dba_RestoreDatabase(
    @targetDbName      sysname      ,
    @sourceDbName      sysname      ,
    @backupdir         varchar(255) ,
    @targetDataDir     varchar(255) ,
    @targetLogDir      varchar(255) ,
    @initialise        bit          = 0,
    @executeCommands   bit          = 0,
    @dbBkpExtension    varchar(10)  = 'bak',
    @logBkpExtension   varchar(10)  = 'trn',
    @orderedFilenames  bit          = 0)
as
set nocount on
declare
  @cmd varchar(8000),
  @file varchar(255),
  @lastFullBackup datetime,
  @lastDiffBackup datetime,
  @backupfile varchar(255),
  @fileTimestamp datetime,
  @logicalName sysname,
  @fileGroupName sysname,
  @physicalName varchar(255),
  @lastFileRestored varchar(255),
  @lastRestoreStatus int,
  @backupCreationDate datetime

---------------------------------------------------------------

-- Table to store restore progress

if object_id('DBAdmin..dba_RestoreStatus') is null
  create table DBAdmin..dba_RestoreStatus (
    id int identity primary key,
    DatabaseName sysname,
    LastFileRestored varchar(255),
    BackupCreationDate datetime,
    LastRestoreStartDate datetime,
    LastRestoreFinishDate datetime,
    RestoreStatus int)

---------------------------------------------------------------

-- Get list of files in folder

if object_id('tempdb..#output') is not null
  drop table #output
create table #output (line varchar(8000))

set @cmd = 'dir /b "' + @backupdir + '*.' + @dbBkpExtension +
           '" "' + @backupdir + '*.' + @logBkpExtension + '"'

insert into #output
exec master..xp_cmdshell @cmd

-- If not a full restore get rid of uneccessary stuff

delete from #output where line is null
if @initialise = 0
begin
  select
    @lastFileRestored = LastFileRestored,
    @lastRestoreStatus = RestoreStatus,
    @backupCreationDate = BackupCreationDate
  from DBAdmin..dba_RestoreStatus
  where DatabaseName = @targetDbName

  -- If file names are ordered remove old files based on name

  if @orderedFilenames = 1
  begin
    if @lastRestoreStatus = 0
      delete from #output
      where line <= right(@lastFileRestored,
                    charindex('', reverse(@lastFileRestored))-1)
    else
      delete from #output
      where line < right(@lastFileRestored,
                   charindex('', reverse(@lastFileRestored))-1)
  end
end

-- Table to store restore headeronly results

if object_id('tempdb..#headers') is not null drop table #headers
  create table #headers (
    FileName                varchar(255)     ,
    BackupName              sysname          null,
    BackupDescription       sysname          null,
    BackupType              int              null,
    ExpirationDate          datetime         null,
    Compressed              bit              null,
    Position                int              null,
    DeviceType              int              null,
    UserName                sysname          null,
    ServerName              sysname          null,
    DatabaseName            sysname          null,
    DatabaseVersion         int              null,
    DatabaseCreationDate    datetime         null,
    BackupSize              numeric(38)      null,
    FirstLsn                numeric(38)      null,
    LastLsn                 numeric(38)      null,
    CheckpointLsn           numeric(38)      null,
    DifferentialBaseLsn     numeric(38)      null,
    BackupStartDate         datetime         null,
    BackupFinishDate        datetime         null,
    SortOrder               int              null,
    CodePage                int              null,
    UnicodeLocaleId         int              null,
    UnicodeComparisonStyle  int              null,
    CompatibilityLevel      int              null,
    SoftwareVendorId        int              null,
    SoftwareVersionMajor    int              null,
    SoftwareVersionMinor    int              null,
    SoftwareVersionBuild    int              null,
    MachineName             sysname          null,
    Flags                   int              null,
    BindingId               uniqueidentifier null,
    RecoveryForkId          uniqueidentifier null,
    Collation               sysname          null,
    FamilyGUID              uniqueidentifier null,
    HasBulkLoggedData       bit              null,
    IsSnapshot              bit              null,
    IsReadOnly              bit              null,
    IsSingleUser            bit              null,
    HasBackupChecksums      bit              null,
    IsDamaged               bit              null,
    BeginsLogChain          bit              null,
    HasIncompleteMetaData   bit              null,
    IsForceOffline          bit              null,
    IsCopyOnly              bit              null,
    FirstRecoveryForkID     uniqueidentifier null,
    ForkPointLSN            numeric(38)      null,
    RecoveryModel           sysname          null,
    DatabaseBackupLSN       numeric(38)      null,
    DifferentialBaseGUID    uniqueidentifier null,
    BackupTypeDescription   sysname          null,
    BackupSetGUID           uniqueidentifier null)

-- Get information for each of the files

while 1 = 1
begin
  select top 1 @file = line
  from #output
  where @file is null or line > @file
  order by line
  if @@rowcount <> 1 break

  set @cmd = 'restore headeronly from disk = ''' +
             @backupdir + '' + @file + ''''

  if @@version like '%8.00.%'
    insert into #headers
      (BackupName, BackupDescription, BackupType, ExpirationDate,
       Compressed, Position, DeviceType, UserName, ServerName,
       DatabaseName, DatabaseVersion, DatabaseCreationDate,
       BackupSize, FirstLsn, LastLsn, CheckpointLsn,
       DifferentialBaseLsn, BackupStartDate, BackupFinishDate,
       SortOrder, CodePage, UnicodeLocaleId, UnicodeComparisonStyle,
       CompatibilityLevel, SoftwareVendorId, SoftwareVersionMajor,
       SoftwareVersionMinor, SoftwareVersionBuild, MachineName,
       Flags, BindingId, RecoveryForkId, Collation)
    execute ( @cmd )
  else
    insert into #headers
      (BackupName, BackupDescription, BackupType, ExpirationDate,
       Compressed, Position, DeviceType, UserName, ServerName,
       DatabaseName, DatabaseVersion, DatabaseCreationDate,
       BackupSize, FirstLsn, LastLsn, CheckpointLsn,
       DatabaseBackupLSN,   BackupStartDate, BackupFinishDate,
       SortOrder, CodePage, UnicodeLocaleId, UnicodeComparisonStyle,
       CompatibilityLevel, SoftwareVendorId, SoftwareVersionMajor,
       SoftwareVersionMinor, SoftwareVersionBuild, MachineName,
       Flags, BindingId, RecoveryForkId, Collation, FamilyGUID,
       HasBulkLoggedData, IsSnapshot, IsReadOnly, IsSingleUser,
       HasBackupChecksums, IsDamaged, BeginsLogChain,
       HasIncompleteMetaData, IsForceOffline, IsCopyOnly,
       FirstRecoveryForkID, ForkPointLSN, RecoveryModel,
       DifferentialBaseLSN, DifferentialBaseGUID,
       BackupTypeDescription, BackupSetGUID)
    execute ( @cmd )

  update #headers
  set FileName = @backupdir + '' + @file
  where FileName is null
end

-- If this is a full restore, process fulls and diffs

if @initialise = 1
begin

  -- Get last full backup

  set @lastFullBackup = null
  select @lastFullBackup = max(BackupStartDate)
  from #headers
  where DatabaseName = @sourceDbName
    and BackupType = 1

  if @lastFullBackup is null
  begin
    raiserror('Couldn''t find a full backup for database [%s]',
              16, 1, @sourceDbName) with nowait
    return
  end

      -- Restore FULL backup NORECOVERY

      select
        @backupfile = FileName,
        @backupCreationDate = BackupStartDate
      from #headers
      where DatabaseName = @sourceDbName
        and BackupType = 1
        and BackupStartDate = @lastFullBackup

      if object_id('tempdb..#dbfiles') is not null
        drop table #dbfiles

      create table #dbfiles (
        LogicalName          sysname          ,
        PhysicalName         varchar(255)     ,
        Type                 char(1)          null,
        FileGroupName        sysname          null,
        Size                 numeric(38)      null,
        MaxSize              numeric(38)      null,
        FileId               int              null,
        CreateLSN            numeric(38)      null,
        DropLSN              numeric(38)      null,
        UniqueId             uniqueidentifier null,
        ReadOnlyLSN          numeric(38)      null,
        ReadWriteLSN         numeric(38)      null,
        BackupSizeInBytes    bigint           null,
        SourceBlockSize      bigint           null,
        FileGroupId          int              null,
        LogGroupGUID         uniqueidentifier null,
        DifferentialBaseLSN  numeric(38)      null,
        DifferentialBaseGUID uniqueidentifier null,
        IsReadOnly           bit              null,
        IsPresent            bit              null)

      set @cmd = 'restore filelistonly from disk = ''' +
                 @backupfile + ''''

      if @@version like '%8.00.%'
        insert into #dbfiles
          (LogicalName, PhysicalName, Type,
           FileGroupName, Size, MaxSize)
        execute ( @cmd )
      else
        insert into #dbfiles
          (LogicalName, PhysicalName, Type,
           FileGroupName, Size, MaxSize, FileId,
           CreateLSN, DropLSN, UniqueId, ReadOnlyLSN,
           ReadWriteLSN, BackupSizeInBytes, SourceBlockSize,
           FileGroupId, LogGroupGUID, DifferentialBaseLSN,
           DifferentialBaseGUID, IsReadOnly, IsPresent)
        execute ( @cmd )

      set @cmd = 'restore database [' + @targetDbName + ']' +
                 ' from disk = ''' + @backupfile +
                 ''' with stats = 1, norecovery'

      declare @count int
      set @logicalName = null
      set @count = 1
      while 1 = 1
      begin
        select top 1
          @logicalName = LogicalName,
          @fileGroupName = FileGroupName,
          @physicalName = PhysicalName
        from #dbfiles
        where @logicalName is null
           or LogicalName > @logicalName
        order by LogicalName
        if @@rowcount <> 1 break

        if @fileGroupName is not null
          set @cmd = @cmd + ', move ''' + @logicalName +
                     ''' to ''' + @targetDataDir + '' +
                     @targetDbName + '_' + @fileGroupName +
                     convert(varchar, @count) + '.' +
                     right(@physicalName,
                     charindex('.',reverse(@physicalName))-1) +
                     ''''
        else
          set @cmd = @cmd + ', move ''' + @logicalName +
                     ''' to ''' + @targetLogDir + '' +
                     @targetDbName + '_Log' +
                     convert(varchar, @count) + '.' +
                     right(@physicalName,
                     charindex('.',reverse(@physicalName))-1) +
                     ''''

        set @count = @count + 1
      end

      if @executeCommands = 1
      begin
        if not exists (select 1
                       from DBAdmin..dba_RestoreStatus
                       where DatabaseName = @targetDbName)
          insert into DBAdmin..dba_RestoreStatus
            (DatabaseName, LastFileRestored, BackupCreationDate,
             LastRestoreStartDate, LastRestoreFinishDate,
             RestoreStatus)
          values (@targetDbName, @backupfile, @backupCreationDate,
                  getdate(), null, null)
        else
          update DBAdmin..dba_RestoreStatus
          set LastFileRestored = @backupfile,
              BackupCreationDate = @backupCreationDate,
              LastRestoreStartDate = getdate(),
              LastRestoreFinishDate = null,
              RestoreStatus = null
          where DatabaseName = @targetDbName
      end

      print @cmd
      if @executeCommands = 1 execute ( @cmd )

      if @executeCommands = 1
      begin
        update DBAdmin..dba_RestoreStatus
        set LastRestoreFinishDate = getdate(),
            RestoreStatus = @@error
        where DatabaseName = @targetDbName
      end

  -- Get last differential backup

  set @lastDiffBackup = null
  select @lastDiffBackup = max(BackupStartDate)
  from #headers
  where DatabaseName = @sourceDbName
    and BackupType = 5
    and BackupStartDate > @lastFullBackup

      -- Restore DIFFERENTIAL backup NORECOVERY

      if @lastDiffBackup is not null
      begin
        set @backupfile = null
        select @backupfile = FileName
        from #headers
        where DatabaseName = @sourceDbName
          and BackupType = 5
          and BackupStartDate = @lastDiffBackup

        truncate table #dbfiles

        set @cmd = 'restore filelistonly from disk = ''' +
                   @backupfile + ''''
        if @@version like '%8.00.%'
          insert into #dbfiles
            (LogicalName, PhysicalName, Type,
             FileGroupName, Size, MaxSize)
          execute ( @cmd )
        else
          insert into #dbfiles
            (LogicalName, PhysicalName, Type,
             FileGroupName, Size, MaxSize, FileId,
             CreateLSN, DropLSN, UniqueId, ReadOnlyLSN,
             ReadWriteLSN, BackupSizeInBytes, SourceBlockSize,
             FileGroupId, LogGroupGUID, DifferentialBaseLSN,
             DifferentialBaseGUID, IsReadOnly, IsPresent)
          execute ( @cmd )

        if @executeCommands = 1
        begin
          update DBAdmin..dba_RestoreStatus
          set LastFileRestored = @backupfile,
              BackupCreationDate = @backupCreationDate,
              LastRestoreStartDate = getdate(),
              LastRestoreFinishDate = null,
              RestoreStatus = null
          where DatabaseName = @targetDbName
        end

        set @cmd = 'restore database [' + @targetDbName + ']' +
                   ' from disk = ''' + @backupfile +
                   ''' with stats = 1, norecovery'

        print @cmd
        if @executeCommands = 1 execute ( @cmd )

        if @executeCommands = 1
        begin
          update DBAdmin..dba_RestoreStatus
          set LastRestoreFinishDate = getdate(),
              RestoreStatus = @@error
          where DatabaseName = @targetDbName
        end
      end

end

-- Process log backups

if @initialise = 1
  set @backupCreationDate = null
else
  select @backupCreationDate = BackupCreationDate
  from DBAdmin..dba_RestoreStatus

set @fileTimestamp = null
while 1 = 1
begin
  select top 1
    @fileTimestamp = BackupStartDate,
    @backupfile = FileName,
    @backupCreationDate = BackupStartDate
  from #headers
  where DatabaseName = @sourceDbName
    and BackupType = 2
    and (@backupCreationDate is null
         or BackupStartDate > @backupCreationDate)
    and (@lastFullBackup is null
         or BackupStartDate > @lastFullBackup)
    and (@lastDiffBackup is null
         or BackupStartDate > @lastDiffBackup)
    and (@fileTimestamp is null
         or BackupStartDate > @fileTimestamp)
  order by BackupStartDate
  if @@rowcount <> 1 break

  if @executeCommands = 1
  begin
    if not exists (select 1
                   from DBAdmin..dba_RestoreStatus
                   where DatabaseName = @targetDbName)
      insert into DBAdmin..dba_RestoreStatus
        (DatabaseName, LastFileRestored, LastRestoreStartDate,
         LastRestoreFinishDate, RestoreStatus)
      values (@targetDbName, @backupfile, getdate(), null, null)
    else
      update DBAdmin..dba_RestoreStatus
      set LastFileRestored = @backupfile,
          BackupCreationDate = @backupCreationDate,
          LastRestoreStartDate = getdate(),
          LastRestoreFinishDate = null,
          RestoreStatus = null
      where DatabaseName = @targetDbName
  end

  -- Restore LOG backup NORECOVERY

  set @cmd = 'restore log [' + @targetDbName + ']' +
             ' from disk = ''' + @backupfile +
             ''' with stats = 1, norecovery'

  print @cmd
  if @executeCommands = 1 execute ( @cmd )

  if @executeCommands = 1
    update DBAdmin..dba_RestoreStatus
    set LastRestoreFinishDate = getdate(),
        RestoreStatus = @@error
    where DatabaseName = @targetDbName

end
go