SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 How Backup Works
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/22/2004 :  16:05:25  Show Profile  Reply with Quote
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

Edited by - Kristen on 07/23/2004 12:24:13

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 07/22/2004 :  16:17:20  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/22/2004 :  16:59:23  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 07/22/2004 :  19:34:53  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/23/2004 :  01:12:15  Show Profile  Reply with Quote
(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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2004 :  01:39:41  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/23/2004 :  04:41:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2004 :  11:29:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000