def getBackupInfo(databaseName):
getDbBackupscript = u"""
declare @cmt varchar(200)
set @cmt = ''
declare @dbname varchar(250)
set @dbname = '{database_name}'
print @cmt+'database '+@dbname
print @cmt+'full backup of '+@dbname
declare @full_bs_id numeric(38)
declare @full_start datetime
declare @full_end datetime
declare @full_created bit
declare @full_lsn numeric(38)
declare @full_media_id numeric(38)
declare @full_media_file varchar(2000)
declare @full_media_pos numeric(38)
set @full_created = 0
select top 1
@full_created = 1,
@full_bs_id = backup_set_id,
@full_start = backup_start_date,
@full_end = backup_finish_date,
@full_lsn = database_backup_lsn,
@full_media_pos = position,
@full_media_id = media_set_id,
@full_media_file = (select b.physical_device_name from msdb.dbo.backupmediafamily b where b.media_set_id = bs.media_set_id)
from msdb.dbo.backupset bs
where
bs.database_name = @dbname
and bs.type = 'D'
order by bs.backup_start_date desc
if @full_created = 1 begin
print @cmt+'full backupset id: '+cast(@full_bs_id as varchar)
print @cmt+'full backupset started: '+convert(varchar(200),@full_start,120) -- +cast(@full_start as varchar)
print @cmt+'full backupset finished: '+convert(varchar(200),@full_end,120) -- +cast(@full_end as varchar)
print @cmt+'full backupset db lsn: '+cast(@full_lsn as varchar)
print @cmt+'full backupset media id: '+cast(@full_media_id as varchar)
print @cmt+'full media file: '+@full_media_file
print @cmt+'full media position: '+cast(@full_media_pos as varchar)
print @cmt+'diff backup of '+@dbname
declare @diff_bs_id numeric(38)
declare @diff_start datetime
declare @diff_end datetime
declare @diff_created bit
declare @diff_lsn numeric(38)
declare @diff_media_id numeric(38)
declare @diff_media_file varchar(2000)
declare @diff_media_pos numeric(38)
set @diff_created = 0
select top 1
@diff_created = 1,
@diff_bs_id = backup_set_id,
@diff_start = backup_start_date,
@diff_end = backup_finish_date,
@diff_lsn = database_backup_lsn,
@diff_media_pos = position,
@diff_media_id = media_set_id,
@diff_media_file = (select b.physical_device_name from msdb.dbo.backupmediafamily b where b.media_set_id = bs.media_set_id)
from msdb.dbo.backupset bs
where
bs.database_name = @dbname
and bs.type = 'I'
and bs.database_backup_lsn > @full_lsn
order by bs.backup_start_date desc
if @diff_created = 1 begin
print @cmt+'diff backupset id: '+cast(@diff_bs_id as varchar)
print @cmt+'diff backupset started: '+convert(varchar(200),@diff_start,120) -- +cast(@diff_start as varchar)
print @cmt+'diff backupset finished: '+convert(varchar(200),@diff_end,120) -- +cast(@diff_end as varchar)
print @cmt+'diff backupset db lsn: '+cast(@diff_lsn as varchar)
print @cmt+'diff backupset media id: '+cast(@diff_media_id as varchar)
print @cmt+'diff media file: '+@diff_media_file
print @cmt+'diff media position: '+cast(@diff_media_pos as varchar)
end
end else begin
print @cmt+'! full backup of '+@dbname+' not exists'
end
go
""".format(
database_name=databaseName
)
res = sqlexec(getDbBackupscript)
sqlprcStdoutLines = re.split("\r\n|\n|\r", res.stdOut)
res = Foo()
full = Foo()
fullMedia = Foo()
diff = Foo()
diffMedia = Foo()
fullIdmatched = False
fullMediaIdmatched = False
diffIdmatched = False
diffMediaIdmatched = False
for line in sqlprcStdoutLines:
# full
m1 = re.match("full\s+backupset\s+id\s*:\s*(\d+)", line)
if m1:
full.id = m1.groups()[0]
fullIdmatched = True
m1 = re.match("full\s+backupset\s+started\s*:\s*(.+)", line)
if m1:
full.started = m1.groups()[0]
m1 = re.match("full\s+backupset\s+finished\s*:\s*(.+)", line)
if m1:
full.finished = m1.groups()[0]
m1 = re.match("full\s+backupset\s+db\s+lsn\s*:\s*(.+)", line)
if m1:
full.lsn = m1.groups()[0]
m1 = re.match("full\s+backupset\s+media\s+id\s*:\s*(.+)", line)
if m1:
fullMedia.id = m1.groups()[0]
fullMediaIdmatched = True
m1 = re.match("full\s+media\s+file\s*:\s*(.+)", line)
if m1:
if m1.groups()[0] in fix_backup_path_replace:
fullMedia.file = fix_backup_path_replace[m1.groups()[0]]
else:
fullMedia.file = m1.groups()[0]
m1 = re.match("full\s+media\s+position\s*:\s*(.+)", line)
if m1:
fullMedia.position = m1.groups()[0]
# diff
m1 = re.match("diff\s+backupset\s+id\s*:\s*(\d+)", line)
if m1:
diff.id = m1.groups()[0]
diffIdmatched = True
m1 = re.match("diff\s+backupset\s+started\s*:\s*(.+)", line)
if m1:
diff.started = m1.groups()[0]
m1 = re.match("diff\s+backupset\s+finished\s*:\s*(.+)", line)
if m1:
diff.finished = m1.groups()[0]
m1 = re.match("diff\s+backupset\s+db\s+lsn\s*:\s*(.+)", line)
if m1:
diff.lsn = m1.groups()[0]
m1 = re.match("diff\s+backupset\s+media\s+id\s*:\s*(.+)", line)
if m1:
diffMedia.id = m1.groups()[0]
diffMediaIdmatched = True
m1 = re.match("diff\s+media\s+file\s*:\s*(.+)", line)
if m1:
if m1.groups()[0] in fix_backup_path_replace:
diffMedia.file = fix_backup_path_replace[m1.groups()[0]]
else:
diffMedia.file = m1.groups()[0]
m1 = re.match("diff\s+media\s+position\s*:\s*(.+)", line)
if m1:
diffMedia.position = m1.groups()[0]
if not fullIdmatched:
return None
if fullMediaIdmatched:
full.media = fullMedia
res.full = full
if diffIdmatched:
res.diff = diff
if diffMediaIdmatched:
res.diff.media = diffMedia
return res
