Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- Name: sp_MSextractlastlsnfrombackup

-- Description: This is a helper procedure for extracting the last lsn
--              from a backup device.

-- Parameters: @backupdevicetype   nvarchar(20) (optional, default = 'logical')
--             @backupdevicename   nvarchar(1000) (mandatory)
--             @mediapassword      sysname (optional, default = null)
--             @password           sysname (optional, default = null)
--             @fileidhint         int     (optional, default = null)
--             @unload             bit     (optional, default = 0)
--             @backupservername   sysname (mandatory)
--             @backupdatabasename sysname (mandatory)
--             @lastlsn            binary(10) output (mandatory)

-- Notes: 1) Need more experimentation to find out the quirks of
--           RESTORE HEADERONLY (e.g. do we need to have the initial media in
--           the backup device(s) in order to read the header?)
--        2) Parameters validated in this procedure:
--             @backupdevicetype: Must be one of 'logical', 'disk', or 'tape'
--             @backupdevicename: Cannot be empty.
--             @unload: Cannot be null.
--             @backupservername: Cannot be empty.
--             @backupdatabase: Cannot be empty.
--        3) If there are multiple backup "files" in the specified backup
--           device for the given server:database, the largest lsn among these
--           backup "files" will be returned.
--        4) Only backup headers for the specified server:database
--           will be considered by this procedure.

-- Security: This is an internal system procedure.

create procedure sys.sp_MSextractlastlsnfrombackup (
    @backupdevicetype   nvarchar(20) = 'logical',
    @backupdevicename   nvarchar(1000),
    @mediapassword      sysname = null,
    @password           sysname = null,
    @fileidhint         int = null,
    @unload             bit = 1,
    @backupservername   sysname,
    @backupdatabase     sysname,
    @lastlsn            binary(10) output
    set nocount on
    declare @retcode int,
            @table_created bit,
            @restore_command nvarchar(4000)

    select @retcode = 0,
           @table_created = 0,
           @restore_command = N'',
           @lastlsn = null

    select @backupdevicetype = ltrim(rtrim(lower(isnull(@backupdevicetype,N'') collate SQL_Latin1_General_CP1_CS_AS))),
           @backupdevicename = ltrim(rtrim(isnull(@backupdevicename,N'')))

    -- Parameter check: @backupdevicetype. Must be one of 'logical', 'disk', or 'tape'
    if @backupdevicetype not in (N'logical', N'disk', N'tape')
        return 1

    -- Parameter check: @backupdevicename. Cannot be empty.
    if @backupdevicename = N''
        raiserror(14043,16,-1,'@backupdevicename', 'sp_MSextractlastlsnfrombackup')
        return 1

    -- Parameter check: @backupservername. Cannot be empty.
    if @backupservername = N'' or @backupservername is null
        raiserror(14043,16,-1,'@backupservername', 'sp_MSextractlastlsnfrombackup')
        return 1

    -- Parameter check: @backupdatabase. Cannot be empty.
    if @backupdatabase = N'' or @backupdatabase is null
        raiserror(14043,16,-1,'@backupdatabase', 'sp_MSextractlastlsnfrombackup')
        return 1

    -- Parameter check: @unload. Cannot be null.
    if @unload is null
        raiserror(14043,16,-1,'@unload', 'sp_MSextractlastlsnfrombackup')
        return 1

    -- Create temp table to hold the result set of 'restore headeronly'
    create table #restore_headeronly (
        BackupName             nvarchar(128) null,
        BackupDescription      nvarchar(255) null,
        BackupType             smallint null,
        ExpirationDate datetime null,
        Compressed             tinyint null,
        Position               smallint null,
        DeviceType             tinyint null,
        UserName               nvarchar(128) null,
        ServerName             nvarchar(128) null,
        DatabaseName           nvarchar(128) null,
        DatabaseVersion        int null,
        DatabaseCreationDate   datetime null,
        BackupSize             numeric(20,0) null,
        FirstLsn               numeric(25,0) null,
        LastLsn                numeric(25,0) null,
        CheckpointLsn          numeric(25,0) null,
        DatabaseBackupLsn      numeric(25,0) null,
        BackupStartDate        datetime null,
        BackupFinishDate       datetime null,
        SortOrder              smallint null,
        CodePage               smallint null,
        UnicodeLocaleId        int null,
        UnicodeComparisonStyle int null,
        CompatibilityLevel     tinyint null,
        SoftwareVendorId       int null,
        SoftwareVersionMajor   int null,
        SoftwareVersionMinor   int null,
        SoftwareVersionBuild   int null,
        MachineName            nvarchar(128) null,
        Flags                  int null,
        BindingID              uniqueidentifier null,
        RecoveryForkID         uniqueidentifier null,
        Collation              nvarchar(128) 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(25,0) null,
        RecoveryModel          nvarchar(60) null,
        DifferentialBaseLSN    numeric(25,0) null,
        DifferentialBaseGUID   uniqueidentifier null,
        BackupTypeDescription  nvarchar(60) null,
        BackupSetGUID          uniqueidentifier null,
        CompressedBackupSize   numeric(20,0) null)

    if @@error<>0 begin select @retcode = 1 goto Failure end
    select @table_created = 1

    -- construct the restore headeronly command
    select @restore_command = N'restore headeronly from ' +
        case @backupdevicetype
            when N'logical' then quotename(@backupdevicename) collate database_default
            when N'disk' then N'disk = ' + sys.fn_replmakestringliteral(@backupdevicename) collate database_default
            when N'tape' then N'tape = ' + sys.fn_replmakestringliteral(@backupdevicename) collate database_default
        end +
        -- backup device name
        N' with ' +
        -- unload | nounload
        case @unload
            when 0 then 'nounload'
            when 1 then 'unload'
        end +
        -- fileid
            when @fileidhint is not null
                then N', file = ' + convert(nvarchar(25), @fileidhint)
            else N''
        end +
        -- password
            when @password is not null
                then N', password = ' + sys.fn_replmakestringliteral(@password) collate database_default
            else N''
        end +
        -- mediapassword
            when @mediapassword is not null
                then N', mediapassword = ' + sys.fn_replmakestringliteral(@mediapassword) collate database_default
            else N''
    if @@error<>0 begin select @retcode = 1 goto Failure end

    insert into #restore_headeronly exec(@restore_command)
    if @@error<>0 begin select @retcode = 1 goto Failure end

    select top 1 @lastlsn = sys.fn_convertnumericlsntobinary10(LastLsn)
      from #restore_headeronly
     where upper(ServerName collate database_default) = upper(@backupservername)
       and DatabaseName = @backupdatabase
     order by LastLsn desc

    if @@error<>0 begin select @retcode = 1 goto Failure end

    if @lastlsn is null
        declare @quoteddb nvarchar(4000)
        select @quoteddb = sys.fn_replquotename(@backupservername, default) collate database_default + N'.' +
                           sys.fn_replquotename(@backupdatabase, default) collate database_default
        select @retcode = 1
        goto Failure


    if @table_created = 1
        drop table #restore_headeronly

    return @retcode

Last revision 2008RTM
See also

  sp_MSaddautonosyncsubscription (Procedure)
sp_MSdrop_repltran (Procedure)


  Query a named instance
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash