• 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