-- 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
    )
as
begin
    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')
    begin
        raiserror(18781,16,-1)
        return 1
    end

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

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

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

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

    -- 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
        case
            when @fileidhint is not null
                then N', file = ' + convert(nvarchar(25), @fileidhint)
            else N''
        end +
        -- password
        case
            when @password is not null
                then N', password = ' + sys.fn_replmakestringliteral(@password) collate database_default
            else N''
        end +
        -- mediapassword
        case
            when @mediapassword is not null
                then N', mediapassword = ' + sys.fn_replmakestringliteral(@mediapassword) collate database_default
            else N''
        end
    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
    begin
        declare @quoteddb nvarchar(4000)
        select @quoteddb = sys.fn_replquotename(@backupservername, default) collate database_default + N'.' +
                           sys.fn_replquotename(@backupdatabase, default) collate database_default
        raiserror(18782,16,-1,@quoteddb)
        select @retcode = 1
        goto Failure
    end

Failure:

    if @table_created = 1
    begin
        drop table #restore_headeronly
    end

    return @retcode
end