May 13, 2008 at 9:18 am
Example:
Let's say I have a 500GB database. A full backup takes 5hrs to complete. I start the full backup at 12AM.
I run transaction log backups every 20 minutes. These take 1min to run.
The full backup runs between 12AM and 5AM. Transaction log backups run at 12AM, 12:20AM, 12:40AM, 1AM and so on. There will be 15 transaction log backups taken in the same period of time the full backup is being taken.
So my questions:
Does SQL Server only backup the state of the DB at the time the full backup starts or does the full backup reflect changes made during the time the backup is being made?
If the full backup reflects changes, where does the full backup end and transaction log backups begin? Do transaction logs hold duplicate data somehow?
I understand that transaction log files and data files are completely different things. But I'm confused on how the different backups handle changes to the db.
Thanks,
Rob
May 13, 2008 at 9:42 am
To my knowledge SQL Server will not allow you to run a full backup and a transaction log backup concurrently so whichever one that starts first will "win".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2008 at 9:53 am
I thought that was true for SQL Server 2000 but not 2005?
Check out this table:
http://msdn.microsoft.com/en-us/library/ms189315.aspx
From this I gather that the two run concurrently. I could be misunderstanding this though.
The example I gave is pretty much straight out of my current environment. I'm wondering how this setup affects backup and recovery and if I should stop transaction log backups while the full backup runs.
Thanks,
Rob
May 13, 2008 at 10:14 am
See this is why I always post, I can be wrong and learn something. Here is what I tried before I answered though.
Switched AdventuteWorks to Full Recovery
Started a Full Backup
Started a TX Backup
Full completed in 16 seconds
TX took 14+ (HAd to switch SSMS tabs)
Ran TX Log Backup again and was subsecond.
It certainly seemed like the TX Log waited for the Full to complete. Maybe it just does not fail like it used to in 2000?
It would be interesting to see if you could restore from the TX Log backups run while the Full is running. Is the system active during the backup or is it a "9 to 5" system? If it is a "9 to 5" system I would stop the TX log backups, if not then I would keep them running since if you have a failure during the 5 hour full backup window you could restore from the previous full and then apply TX Logs, I think.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2008 at 10:42 am
This is an auditing database so it has to run 24x7 with a low exposure to work loss.
Hmm... I think your test is good but tough to prove out only because AdventureWorks is so small and there are prob no transactions occurring.
What if I use RESTORE HEADERONLY on my full backup and transaction logs during that time and check the LSN numbers? Maybe that will shed some light.
RESTORE HEADERONLY doesn't actually change anything right? In other words, it is safe for me to do through management studio?
May 13, 2008 at 11:27 am
I tried to figure out how this works some time ago. Here are some tests that I did. It seems like log backups can complete while the database backup is running. They do however take a longer time to run. I'm using SQL Server 2005 SP2.
Connection 1, Inserts into a table in a loop
Connection 2, Log Backups in a loop with a 1 second delay between each backup
2008-04-25 11:11:28
Processed 441 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 441 pages in 0.619 seconds (5.827 MB/sec).
2008-04-25 11:11:28
2008-04-25 11:11:29
Processed 296 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 296 pages in 52.446 seconds (0.046 MB/sec).
2008-04-25 11:12:22
2008-04-25 11:12:23
Processed 46 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 46 pages in 50.094 seconds (0.007 MB/sec).
2008-04-25 11:13:14
2008-04-25 11:13:15
Processed 48 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 48 pages in 43.417 seconds (0.009 MB/sec).
2008-04-25 11:13:59
2008-04-25 11:14:00
Processed 50 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 50 pages in 31.013 seconds (0.012 MB/sec).
2008-04-25 11:14:32
2008-04-25 11:14:33
Processed 369 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP LOG successfully processed 369 pages in 0.433 seconds (6.970 MB/sec).
2008-04-25 11:14:33
Connection 3, A full backup
2008-04-25 11:11:29
Processed 316904 pages for database 'TestDb', file 'TestDb' on file 1.
Processed 186 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP DATABASE successfully processed 317090 pages in 182.725 seconds
(14.215 MB/sec).
2008-04-25 11:14:32
Ola Hallengren
May 13, 2008 at 12:11 pm
Hi Ola.
For each transaction into SQL Server 2005, my (admittedly naive) understanding of the process is that SQL will:
1. pull pages that needs to be modified into the buffer cache
2. update pages in the buffer cache
3. update the log cache with the change
4. write the log record to disk (I'm assuming this means LDF)
5. flush dirty buffer cache pages to disk (I'm assuming this means MDF)
Ref: http://msdn.microsoft.com/en-us/library/ms186259.aspx
Does this sound correct?
We see from your testing that indeed you can run bull backups and transaction logs. So that leads me to wonder... if transactions are occurring while full backups and transaction log backups are running, will the full backup reflect changes or do the transaction logs pick up changes? Or both maybe?
It would seem to me that full backup would be based off of some kind of point in time snapshot taken when the backup begins. Because, if the database kept changing and the full backup had to reflect that, how could the full backup ever finish?
Although this seems logical to me, I know I'm incorrect. Which is why I'm confused 😀
Thanks,
Rob
May 13, 2008 at 1:01 pm
I think that we need to do this test again. I would like to analyze the _lsn columns in the backupset table in msdb and also do some restore tests. I'll do this in the next few days.
Ola Hallengren
May 13, 2008 at 3:42 pm
The full backup contains the state/data of the database at the time the full backup FINISHED (including whatever log records are required to get the database in a consistent state).
The log backups will continue running (albeit longer, perhaps, as previously stated).
So if you really wanted to, instead of restoring from the last full backup you could restore from the full backup taken before that and apply all the consequent log backups to get to the same state (I have read somewhere that somebody actually had to do something like this due to corrupted backups).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 14, 2008 at 3:24 am
In SQL 2000 log and full backups were mutually exclusive. I would say this is because the full backup process works by just dumping out the extents in the database sequentially and then using the log records to store any changes that occurred whilst the backup was running, with a final checkpoint to give a consistent state point at the finish of the backup (as stated).
Presuming a SQL 2005 full backup still just dumps out the extents sequentially without caring about modifications (as this is the fastest way to do it), how does it track changes to data whilst the backup is running if it allows tran log backups at the same time? does it use the GAM page, anyone know?
BTW - your full backup will run faster if you stripe it across backup devices.
---------------------------------------------------------------------
May 14, 2008 at 7:01 pm
I don't know what mechanism SQL uses. From the MS SQL Server 2005 Admin Pocket Guide:
[font="Times New Roman"]When the backup starts, SQL Server copies everything in the database and also includes portions of the transaction log that are needed while the backup is in progress.
[/font]
And from Inside SQL Server 2000:
[font="Times New Roman"]To maintain consistency for either a full or a differential backup, SQL Server records the current log sequence number (LSN) at the time the backup starts and then again at the time the backup ends. This allows the backup to also capture the relevant parts of the log. The relevant part starts with the oldest open transaction at the time of the first recorded LSN and ends with the second recorded LSN.
[/font]
How log backups still occur without truncating screwing up the full backup, I don't know. Smoke and mirrors? Magic? Hamster in the machine? :hehe:
Book quotes reproduced without permission.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 14, 2008 at 10:22 pm
"A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished." http://technet.microsoft.com/en-us/library/ms186289(SQL.100).aspx
This logically makes sense. Consider if you backed up your DB, had 5-10 minutes of usage, then the DB goes down. There was not enough time for a tran log backup, so you would have to backup the tail of the log and use the last full backup set to restore from.
The fact is that a full backup contains just that, a full copy of your database up to the end of the backup process. You still need your transaction log backups to run during the full backup in case your system failed during the middle of the backup process.
Here are a my thoughts on how this occurs...
1. Full backup begins by capturing current LSN, then flushes all transactions to database file (rolls database forward).
2. Full DB is then written to disk/backup media.
3. At end of DB backup process, all transactions since first LSN are applied to make the DB current (once again rolls DB forward before closing backup process).
I guess transactions being applied during the backup process could be applied to the backup set simultaneously while backing up the data, but this just doesn't sound efficient.
Now, how does all of this work with SQL 2008 when you specify BACKUP DATABASE....WITH COMPRESSION? :w00t:
May 15, 2008 at 6:16 am
maybe in 2005 log backups that occur whilst full backup is running don't truncate the log?
little hamsters writing it all down more likely though.......
---------------------------------------------------------------------
May 16, 2008 at 11:54 am
I just read about this the other day. When a full backup is started, the current (at that time) LSN is noted. The backup is completed. It notes the current LSN (at the time of completion), and backs up on the tail end all the changes that were done between the two LSN's. This is in the full backup. Therefore, all changes are in the full backup, including the changes that occured during the backup.
Hope this helps.
May 16, 2008 at 12:19 pm
rhlangley (5/16/2008)
I just read about this the other day. When a full backup is started, the current (at that time) LSN is noted. The backup is completed. It notes the current LSN (at the time of completion), and backs up on the tail end all the changes that were done between the two LSN's. This is in the full backup. Therefore, all changes are in the full backup, including the changes that occured during the backup.Hope this helps.
ok, but say betwen those two lsns a number of transactions are commited (including the first lsn), would these not be truncated from the log if a concurrent log backup ran? How does the full backup cope with that?
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply