Restore latest backup from bak file with script

Question

I create backups using the same .bak file. I've made a script to automagically restore this backup.

RESTORE DATABASE [xxx] FROM  DISK = N'xxx.bak' 
WITH  FILE = 10,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Here, the backup set to restore is explicitly specified. However, I always want to restore the latest set available. By default, it uses the first backup set.

Answer 1

Use the RESTORE HEADERONLY command to locate the particular backup you want, as that result set shows the BackupFinishDate. Note the field named Position; that is the FILE number.

At this point, if you already know the logical names, you can run a RESTORE command using the FILE option in the WITH clause.

restore database yourDB
from disk = N'C:\Program Files\Microsoft SQL Server\yourDB.bak' 
with 
  file = 3  

You probably already know that you can use the RESTORE FILELISTONLY command to find the logical names.

Tibor Karaszi has posted a similar (but not same) solution here: http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp You can use his CREATE TABLE commands to get the results of RESTORE HEADERONLY into a table. What I've pasted in below shows how to get the results of RESTORE FILELISTONLY into a table (also ripped from Tibor).

create table FLO_results (
  LogicalName nvarchar(128),
  PhysicalName nvarchar(260),
  [Type] char(1),
  FileGroupName nvarchar(128),
  Size numeric(20,0),
  MaxSize numeric(20,0),
  FileId bigint,
  CreateLSN numeric(25,0),
  DropLSN numeric(25,0),
  UniqueId uniqueidentifier,
  ReadOnlyLSN numeric(25,0),
  ReadWriteLSN numeric(25,0),
  BackupSizeInBytes bigint,
  SourceBlockSize bigint,
  FilegroupId bigint,
  LogGroupGUID uniqueidentifier,
  DifferentialBaseLSN numeric(25),
  DifferentialBaseGUID uniqueidentifier,
  IsReadOnly int,
  IsPresent int
)  
;  
insert into FLO_results 
exec('
  restore filelistonly from disk = ''C:\Program Files\Microsoft SQL Server\yourDB.bak''
')
;
select * from FLO_results
;
drop table FLO_results
;

Answer 2

To augment @Oliver's previous answer. Here's the script (from here) to show the HeaderInfo for your xxx.bak backup.

DECLARE @HeaderInfo table
      (
            BackupName  nvarchar(128),
            BackupDescription  nvarchar(255) ,
            BackupType  smallint ,
            ExpirationDate  datetime ,
            Compressed  bit ,
            Position  smallint ,
            DeviceType  tinyint ,
            UserName  nvarchar(128) ,
            ServerName  nvarchar(128) ,
            DatabaseName  nvarchar(128) ,
            DatabaseVersion  int ,
            DatabaseCreationDate  datetime ,
            BackupSize  numeric(20,0) ,
            FirstLSN  numeric(25,0) ,
            LastLSN  numeric(25,0) ,
            CheckpointLSN  numeric(25,0) ,
            DatabaseBackupLSN  numeric(25,0) ,
            BackupStartDate  datetime ,
            BackupFinishDate  datetime ,
            SortOrder  smallint ,
            CodePage  smallint ,
            UnicodeLocaleId  int ,
            UnicodeComparisonStyle  int ,
            CompatibilityLevel  tinyint ,
            SoftwareVendorId  int ,
            SoftwareVersionMajor  int ,
            SoftwareVersionMinor  int ,
            SoftwareVersionBuild  int ,
            MachineName  nvarchar(128) ,
            Flags  int ,
            BindingID  uniqueidentifier ,
            RecoveryForkID  uniqueidentifier ,
            Collation  nvarchar(128) ,
            FamilyGUID  uniqueidentifier ,
            HasBulkLoggedData  bit ,
            IsSnapshot  bit ,
            IsReadOnly  bit ,
            IsSingleUser  bit ,
            HasBackupChecksums  bit ,
            IsDamaged  bit ,
            BeginsLogChain  bit ,
            HasIncompleteMetaData  bit ,
            IsForceOffline  bit ,
            IsCopyOnly  bit ,
            FirstRecoveryForkID  uniqueidentifier ,
            ForkPointLSN  numeric(25,0) NULL,
            RecoveryModel  nvarchar(60) ,
            DifferentialBaseLSN  numeric(25,0) NULL,
            DifferentialBaseGUID  uniqueidentifier ,
            BackupTypeDescription  nvarchar(60) ,
            BackupSetGUID  uniqueidentifier NULL,
            CompressedBackupSize  numeric(20,0)
      )


INSERT INTO @HeaderInfo EXEC('RESTORE HEADERONLY 
FROM DISK = N''xxx.bak''
WITH NOUNLOAD')

SELECT * FROM @HeaderInfo