Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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"

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
    
Thanks Matt, this set me off on the right foot. –  Jason Jan 28 '10 at 0:05

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.

share|improve this answer
    
I've never understood the benefit of putting multiple backups in one file... –  squillman Jan 27 '10 at 22:08
    
Me either. I think it's a compatibility thing between disk and tape where with tape you want to fill the tape. –  mrdenny Jan 27 '10 at 22:36
    
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. –  Jason Jan 27 '10 at 23:38
    
Agree with mrdenny, don't do it. It doesn't make sense when your backing up to disk. –  Nick Kavadias Jan 27 '10 at 23:40
    
It's even more fun when you have transaction log backups mixed in with full backups in the same file. –  SqlACID Jan 28 '10 at 0:47

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 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.