Solved

SQL Server .BAK files - what's in there exactly?

Posted on 2008-10-10
4
453 Views
Last Modified: 2013-12-03
I'm wondering how SQL Server creates its backups.  Does it always put the same table, index, namespace, etc in the same place in the file?  Can normal use or maintenance activity (like defragging, reindexing) radically alter how the .BAK file gets put together?

The reason I'm asking this is because I'm having some issues with a large file (100+GB) and an offsite backup utility.  Here's the current process:
1.  SQL Server makes a backup to local disk.
2.  Backup server copies the .BAK file to its local cache
3.  Backup server compares it to yesterday's backup at block level and records the differences
4.  Differences are uploaded to offsite storage

This system is working great with all our other data, but this one large file is causing some headaches.  At first it can get the daily changes calculated and moved offsite in a few hours, but over a couple months it takes longer and longer until it's taking over 12 hours.  Then we have to start all over and it's a big hassle.

I'm wondering if it has anything to do with the organization of that .BAK file.  I know that the inside of the .MDF where the live DB lives is very dynamic and a single table can get fragmented over hundreds or thousands of places on the physical disk.  If that organization is reflected in the backup, the daily changes could appear to be huge, even if all we did that day was run some maintenance tasks and the amount of data stored remained unchanged.
0
Comment
Question by:neortho
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Gideon7
ID: 22690510
You are correct in that the MSSQL BAK file works poorly with block-level backup schemes.  This is why the Volume Shadow Service (VSS) on W2K3/W2K8 lets SQL Server implement its own native VSS-writer for backup.

Since you are using a 3rd party backup the best approach is probably to avoid doing a full 100GB backup every night, and instead do daily incremental backups of the transaction log, with full backups at a longer interval.

Example of T-SQL to back up the translaction log:

BACKUP LOG MyDatabase TO DISK = 'E:\MSSQL\BACKUPS\MyDatabase_20081010.trn'
WITH INIT, SKIP, NAME='MyDatabase_20081010.trn',
DESCRIPTION='Backup of log for MyDatabase on 2008/10/10'

A full backup will truncate the log.

To restore, reload the main backup as usual, followed by each log backup starting with the oldest:

RESTORE LOG MyDatabase
FROM DISK='E:\MSSQL\BACKUPS\MyDatabase_20081009.trn'
WITH NORECOVERY -- indicate we want to apply another log

RESTORE LOG MyDatabase
FROM DISK='E:\MSSQL\BACKUPS\MyDatabase_20081010.trn'  -- final log

The log backups will be much smaller, containing only the actual changes since the last full backup.
0
 

Author Comment

by:neortho
ID: 22702895
Thanks for the advice!  I've been meaning to change it to something like that for awhile now.  With all these offsite backup headaches, now might be a good time to go ahead and do it.

I'm still curious as to which DB operations can cause the biggest block level differences from day to day.  Are there any good reference sites you're aware of?  I spent a lot of time searching on Google but couldn't get the results narrowed down enough.
0
 
LVL 12

Accepted Solution

by:
Gideon7 earned 500 total points
ID: 22705269
The .BAK file is a sequential data stream, not a fixed random-access file.  Adding one block of extra data near the beginning of the backup will shove every foillowing block down wrt to previous .BAK file.  This will cause a low-level block-level comparator to mismatch on every single block between the old and new .BAK files, except where they are coincidetally the same, such as two blocks that happen to both be all-zero.
0
 

Author Closing Comment

by:neortho
ID: 31505174
Exactly what I was looking for, and great advice for altering my backup set.  
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Setting up a Microsoft WSUS update system is free relatively speaking if you have hard disk space and processor capacity.   However, WSUS can be a blessing and a curse. For example, there is nothing worse than approving updates and they just have…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now