Home Forums SQL Server 2005 Backups Differential backups getting smaller. RE: Differential backups getting smaller.

  • For what it's worth, you're welcome to use appropriate variations on the code below for your database backups. The backup file names contain timestamps:

    EXECUTE master.dbo.xp_create_subdir N'Z:\Database Backups\Differential Backups\model'

    DECLARE @DateString VARCHAR(16);

    SET @DateString = CONVERT(VARCHAR(16),GETDATE(),126);

    SET @DateString= REPLACE(@DateString,'-','');

    SET @DateString= REPLACE(@DateString,':','');

    SET @DateString= REPLACE(@DateString,'T','');

    EXEC( 'BACKUP DATABASE [model] TO DISK = N''Z:\Database Backups\Differential Backups\model\model_backup_' + @DateString + '.dif'' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N''model_backup_' + @DateString + ''', SKIP, REWIND, NOUNLOAD, STATS = 10, CHECKSUM' );

    DECLARE @backupSetId AS INT

    SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'model' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name=N'model' )

    IF @backupSetId IS NULL BEGIN RAISERROR(N'Verify failed. Backup information for database ''model'' not found.', 16, 1) END

    EXEC( 'RESTORE VERIFYONLY FROM DISK = N''Z:\Database Backups\Differential Backups\model\model_backup_' + @DateString + '.dif'' WITH FILE = ' + @backupSetId + ', NOUNLOAD, NOREWIND, CHECKSUM' );