October 1, 2010 at 11:48 am
I have backup devices created for each of my backups(Full, Diff, Log) and all of my jobs are set to NOINIT(this may change in teh future). I would like to setup a script to run that checks all of the backups in the backup set. Can someone help me with the code to determine the number of backups that exist in the backup set? Thanks.
October 1, 2010 at 1:00 pm
check Restore headeronly in BOL.
It provides the info you need.
You can use it this way:
if (select isnull( object_id(N'tempdb.[dbo].[#Tmp_BackupHeaders]'), 0)) <> 0
begin
DROP table #Tmp_BackupHeaders
end
-- versie SQL2K5 sp2
create table #Tmp_BackupHeaders (
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 uniqueidentifier
, RecoveryForkID uniqueidentifier
, Collation nvarchar(128)
, FamilyGUID uniqueidentifier
, HasBulkLoggedData bit
, IsSnapshot bit
, IsReadOnly bit
, IsSingleUser bit
, HasBackupChecksums bit
, IsDamaged bit
, BeginsLogChain bit
, HasIncompleteMetaData bit
, IsForceOffline bit
, IsCopyOnly bit
, FirstRecoveryForkID uniqueidentifier
, ForkPointLSN numeric(25,0) NULL
, RecoveryModel nvarchar(60)
, DifferentialBaseLSN numeric(25,0) NULL
, DifferentialBaseGUID uniqueidentifier
, BackupTypeDescription nvarchar(60)
, BackupSetGUID uniqueidentifier NULL
)
declare @sql varchar(5000)
set @sql = 'RESTORE HEADERONLY FROM DISK = ''' + @Logbackup_Filename + ''' '
insert into #Tmp_BackupHeaders
exec (@SQL)
Select *
from #Tmp_BackupHeaders
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply