June 13, 2014 at 2:38 am
Hello,
I'm currently looking at differential backups and I'm a bit puzzled about the file sizes it creates.
Every night we do a full backup (SQL 2005 Standard, simple logging mode)
This backup finishes about 22:00 and is 250gb in size. We then do a lot of work via scripts later that evening and early in the morning.
I ran a differential back well after the full backup and script runs finshed at 3am and it produced a 12gb file. I assume here it takes all the changes from the last full backup.
The diff backup I ran, let's say, 10am this morning produced a backup file of 351 mb. Why are these differences between those 2 files so big if they both go back to the last full back up and the second file is so much smaller?
I can see why one is bigger then the other from the output, but why aren't these changes also backed up with the second diff backup?
command:
BACKUP DATABASE [myDatabase] TO DISK = @BACKUP_PATH_FILENAME
WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10
output:
12 gb file:
Message
Executed as user: ###############
10 percent processed. [SQLSTATE 01000] (Message 3211)
20 percent processed. [SQLSTATE 01000] (Message 3211)
30 percent processed. [SQLSTATE 01000] (Message 3211)
40 percent processed. [SQLSTATE 01000] (Message 3211)
50 percent processed. [SQLSTATE 01000] (Message 3211)
60 percent processed. [SQLSTATE 01000] (Message 3211)
70 percent processed. [SQLSTATE 01000] (Message 3211)
80 percent processed. [SQLSTATE 01000] (Message 3211)
90 percent processed. [SQLSTATE 01000] (Message 3211)
Processed 1572024 pages for database 'myDatabase', file 'myDatabase_Data' on file 1.
[SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211)
Processed 1930 pages for database 'myDatabase', file 'myDatabase_Log' on file 1.
[SQLSTATE 01000] (Message 4035)
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 1573954 pages in 978.833 seconds (13.172 MB/sec).
[SQLSTATE 01000] (Message 3014). The step succeeded.
351 mb file:
Message
Executed as user: ###############
10 percent processed. [SQLSTATE 01000] (Message 3211)
20 percent processed. [SQLSTATE 01000] (Message 3211)
30 percent processed. [SQLSTATE 01000] (Message 3211)
40 percent processed. [SQLSTATE 01000] (Message 3211)
50 percent processed. [SQLSTATE 01000] (Message 3211)
60 percent processed. [SQLSTATE 01000] (Message 3211)
70 percent processed. [SQLSTATE 01000] (Message 3211)
80 percent processed. [SQLSTATE 01000] (Message 3211)
90 percent processed. [SQLSTATE 01000] (Message 3211)
Processed 43544 pages for database 'myDatabase', file 'myDatabase_Data' on file 1.
[SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211)
Processed 275 pages for database 'myDatabase', file 'myDatabase_Log' on file 1.
[SQLSTATE 01000] (Message 4035)
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 43819 pages in 38.757 seconds (9.261 MB/sec).
[SQLSTATE 01000] (Message 3014). The step succeeded.
June 13, 2014 at 2:44 am
Are you absolutely sure that something/someone didn't run a full backup between the two?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2014 at 2:50 am
GilaMonster (6/13/2014)
Are you absolutely sure that something/someone didn't run a full backup between the two?
Looking at my output would that be the only possible reason for these massive page count differences?
June 13, 2014 at 2:56 am
Figured it out.
It's the VMware snapshots that is causing this.
So the 12GB is the correct file size back to my full backup all the others are no good to my nightly backup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply