Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 How Backup Works

Author  Topic 

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 16:05:25
EDIT: Subsequent changes marked in blue

I've had a Eureka Moment understanding how backups work. Now, there is a chance that its "just me", and I've been being thick all this time, but in the hope that some other hapless folk have made the same wrong assumptions that I did, I will endeavour to explain what my misunderstandings were in the hope of enabling the rest of the proletariat to "Get It" too.

Point Number One is that there are two ways to clear down SQL's log files (which are normally stored in a file called MyDatabase.LDF or MyDatabase_LOG.LDF)

a) Set the Recovery Model to SIMPLE (Enterprise Manager : Properties : Recovery Model : SIMPLE). Note that this is NOT the default.

Downside of this is that you can NOT recover to a point in time during the day, only to the last full (or differential) backup;
Backups made using the Backup wizard will [falsely] report failure if they include the transaction backup step - e.g. for other databases in the "batch"

b) Do transaction backups

You can do transaction backups as often as you like. Hourly or every 10 minutes seems to be the common choices.

The good news is that you can recover to a point in time (even a point BETWEEN two transaction backups). Of course, if your transaction backups are stored on your Database Server, and it catches fire, then you are out of luck - unless you copied the Transaction Backups to somewhere else.

The other good news is that doing transaction backups means that SQL automatically clears down the Log File.

If you don't do one of these two choices your log files will grow forever - until your disk is full. (And once your disk becomes full it can be VERY DIFFICULT INDEED to get SQL going again). Basically you've either got to do (A) or (B), but out of the box SQL does neither of these and leaves you in deep trouble.

There is also a (C) which is to manually clear down the logs, this shrinks the log file

Now then, the Eureka Moment I mentioned.

I don't know if its just me, but I always worried about the "window of opportunity" between the Transaction Backup and the Full Backup. In my mind the transaction backup was supposed to run shortly before the Full Backup; somehow the transaction backup took everything in the log file and physically posted the committed transaction to the database, and then the full backup ran. I assumed the two were related on some way. And that I was exposed until the next transaction backup ran.

Well, today I've discovered how it works - or I think I have!

The Full Backup and the Transaction Backup have nothing to do with each other (at least in Layman's Terms).

A Full Backup grabs everything it needs to in order to create a backup file which, if restored, will create a new database consistent with the moment-in-time when the backup was made Full Backup finished. I don't know if it causes stuff in the logs to be committed to the database, or not, but I know I don't need to care any more. If I do a full backup every night at 2AM, then I can restore the database to its 2AM state.

A Transaction Backup records everything in the logs since the last transaction backup. It doesn't care about Full Backups. With one exception. If you restore a full backup you can then restore the transaction backup that came after it - even if the full backup was made 2 hours after the previous transaction backup. So there is some "magic" that enables a Transaction Backup to start "restoring" from its middle - i.e. the point at which a Full Backup was made.

If I want to restore the database to how it was at 26 minutes and 17 seconds past 2 this afternoon I've got two choices.

I can restore last night's backup, and then each transaction backup made since then – upto the one after 14:26:17 (I just have to tell RESTORE to stop at 14:26:17)

Or, I can restore the previous night's backup - or last weeks - or last months - and ALL the intervening transaction backups in order since then.

Silly me, I previously thought I had to restore the most recent full backup.

(The transaction log uses Log Sequence Numbers (LSN) for each block of work, a full backup implies that it contains everything up to a particular LSN, and thus restoring a full backup followed by a transaction log restore just uses the LSNs in the transaction log backup file to work out what remains to be restored

I used to worry about developers taking a quick "safety" backup of the database, copying it to their C: drive and deleting it from the SQL box. I thought I had to have that file if I wanted to do a restore - I assumed I had to have the LATEST full backup and then all the transaction backups thereafter. No siree! Any full backup, and all the subsequent transaction backup files, will do just fine.

I can't tell you what a relief this is to know. I can now sleep through the 10 minutes each night that separates the full backup for the transaction backup that follows it - without suddenly waking up in a cold sweat worrying: "Will the server fall over between the Full and transaction backups?"

Night night, sleep well!

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-22 : 16:17:20
Very good information Kristen for those needing to understand backups better. This deservers a blog too. I'm going to keep track of all of the blogs that you are due to write soon.

Tara
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 16:59:23
I did take your advice and asked Graz for one ... there's probably a lot of red tape involved at his end. :)

Kristen
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2004-07-22 : 19:34:53
Just a couple Quich points. You can also explicitly truncate the log as another way to...well.. truncate the log.

Also the "magic" is the LSN (Log Sequence Number). If you look at your error log any time you do a log backup, it will show you the LSNs that backup covers.

I believe the full backup will restor ethe database exactly as it was, not commiting anything that wasn't already commited (Not 100% on that one).


That would really suck if a random backup mucked up your whole backup sequence.

-Chad


http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 01:12:15
(I've merge some More nfo into my original)

Thnking about it maybe the Full Backup does a CheckPoint - which would push stuff into the database proper.

If I have a 3MB database and a 15GB <g> log file, my full backups are still small, but a RESTORE takes forever (and recreates the 15GB log file I think - damm, another experiement to make).

Kristen
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 01:39:41
one nitpick item, the full backup (when restored) creates a database consistent with the time the full back ended, not when it started.




-ec
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-07-23 : 04:41:28
That's cool didn't know that. SO if the backup is running and I have a critical transaction I just need to find a way to pause the backup? <g>

Kristen
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-23 : 11:29:32
quote:
Originally posted by Kristen

That's cool didn't know that. SO if the backup is running and I have a critical transaction I just need to find a way to pause the backup? <g>



Actually, the full backup also includes the transactions that occur during the backup windows. That is why the restored database is consistent to the end time of the full backup. This occurs even if you are in simple mode btw.



-ec
Go to Top of Page
   

- Advertisement -