Sql Server 2005 restore from backup to the most recent file position

I'm trying to restore a database from disk to the most recent backup. There are four backup sets in the backup. I would like to restore the most recent one (taken today). I need to be able to do it with TSQL.

This script below works:

RESTORE DATABASE DatabaseName 
FROM DISK = 'D:\Data\DatabaseName.bak' WITH FILE = 4, REPLACE

However, the file position will change in the future as more backups are made.

Is there a way to restore a backup to a file position without specifying the exact file number? Something like "WITH FILE = most_recent_backup"

From serverfault Jason

• The RESTORE HEADERONLY command will give you a list of data for all backup sets in the file. From there you could select the max Position and pass that into the FILE param.

  http://msdn.microsoft.com/en-us/library/ms178536.aspx

  Jason : Thanks Matt, this set me off on the right foot.
  From MattB

• Don't put all your backups into a single file. If you do and that file becomes corrupt then you have just lost all your backups.

  Instead put each backup into its own file using a dynamic file name when you use the BACKUP DATABASE and RESTORE DATABASE Commands.

  squillman : I've never understood the benefit of putting multiple backups in one file...

  mrdenny : Me either. I think it's a compatibility thing between disk and tape where with tape you want to fill the tape.

  Jason : I think this is the default behavior of Sql Server. I just right-click the database and select Backup and it puts them all in the same file.

  Nick Kavadias : Agree with mrdenny, don't do it. It doesn't make sense when your backing up to disk.

  SqlACID : It's even more fun when you have transaction log backups mixed in with full backups in the same file.

  mrdenny : @Jason correct, it's the default, but that doesn't mean that it is the correct thing to do.
  From mrdenny

• Matt's answer set me off in the right direction. I needed to store the result set before I could select the max position. My search led me to this solution http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=74&threadid=93926&enterthread=y

  set nocount on
  
  Create Table #header (
  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
  )
  
  insert #header
  Exec ('restore headeronly from disk = ''\\pathToBackup\file.bak''')
  
  select backupstartdate from #header
  drop table #header 
  

  From Jason