-- 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