anthony.green (11/15/2012)
Are you writing to the same file on each backup run?
Yes I am.
Okay so I've done a local backup of the model database and the backup file size matches the size in the backupset table. No surprises there.
Somethin'gs not quite right with my process.
Basically I have created a backupschedule table like the below:
I then use a sproc which loops through the record above like this:
BEGIN TRY
-- log the start time of the backup
update QbaseDBAUtility.dbo.backupschedule
set backupstartdate = GETDATE()
where databasename = @DBName
and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))
IF @BackupTtype = 'F'
BEGIN
SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N' + CHAR(39) + @BackupFolderPath + @DBName +
'.complete'' WITH NAME = N' + char(39) + @DBName + ' Complete Backup' + char(39) + ', BUFFERCOUNT = 1024, CHECKSUM'
EXEC(@SQL)
END
ELSE IF @BackupTtype = 'D'
BEGIN
SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N' + CHAR(39) + @BackupFolderPath + @DBName +
'.Differential'' WITH DIFFERENTIAL, NAME = N' + char(39) + @DBName + ' differential backup' + char(39) + ', BUFFERCOUNT = 1024, CHECKSUM'
EXEC (@SQL)
END
-- log the end time of the backup
update QbaseDBAUtility.dbo.backupschedule
set backupenddate = GETDATE(),
BackupStatus = 'Success'
where databasename = @DBName
and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))
-- Let's also get the backup size
update a
set backupSizeMB = b.backup_size / 1048576
from QbaseDBAUtility.dbo.backupschedule as a
inner join msdb.dbo.backupset as b
on a.databasename = b.database_name
where a.databasename = @DBName
and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))
and b.backup_set_id = (select MAX(backup_set_id)
from msdb.dbo.backupset as c
where b.database_name = c.database_name)
END TRY
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn