Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003

    Unanswered: Restore last backup from a device

    I want to be able to take a backup device to another server and then restore the last backup file within the device.

    My script creates the backup device using the filename of the file from the other server but when I do the restore it only restores the first file in the device.

    How can I get it to restore the last file? In EA I can do a manual resotre and look inside the device and choose say backup number 2.

    How can I do this in a script?


  2. #2
    Join Date
    Aug 2002
    Execute RESTORE FILELISTONLY or RESTORE HEADERONLY to see the contents of the file
    Refer to books online for more infomation.
    --Satya SKJ
    Microsoft SQL Server MVP

  3. #3
    Join Date
    Mar 2003
    Yep - got there eventually:

    DECLARE @TargetLocation VARCHAR(1000),
    @Position int

    Set @TargetLocation = 'your_path'

    CREATE TABLE #TempBackupResults
    BackupName nvarchar(128) ,
    BackupDescription nvarchar(255) ,
    BackupType smallint ,
    ExpirationDate datetime ,
    Compressed tinyint ,
    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 nvarchar(128),
    RecoveryForkId nvarchar(128),
    Collation nvarchar(128))

    declare @xpcmdString VARCHAR(2000)

    SELECT @xpcmdString = 'RESTORE HEADERONLY FROM DISK = N' + CHAR(39)+@TargetLocation+CHAR(39)

    Insert #TempBackupResults
    EXEC (@xpcmdString)

    Set @Position = (select Max (Position) from #TempBackupResults)

    Restore database your_db_name

    From disk = 'your_path\filename.bak' with file = @position,


    drop table #TempBackupResults

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts