March 15, 2010 at 8:54 pm
Comments posted to this topic are about the item Full backup Scenario
March 15, 2010 at 9:09 pm
This is a very good question, which definitely required some thinking to answer. Thank you.
It looks like I am the first one who answered this one correctly :-). The full backup is designed to restore the database to the state in which it was at the moment the backup has finished, so the answer makes perfect sense.
Oleg
March 15, 2010 at 11:20 pm
This was removed by the editor as SPAM
March 16, 2010 at 1:39 am
Great question!
One additional comment that was missing from the question and description, is how pending transactions are handled.
IF the session inserting the 10000 rows does so within a transaction, and the transaction is not yet committed when the backup finishes, then a restore from this backup will contain none of those rows.
IF the session inserting the 10000 rows does not use an explicit transaction (making each individual insert statement an implied transaction of its own), then a restore from this backup will contain all the rows that were inserted before the backup finished, and none of the rows inserted after the backup finished.
Even though the portion of the transaction log that is added to the backup will include modifications by uncommitted transactions, the restore process will not restore those changes (or even undo them if they were already made in the data page when it was copied) because the transaction is still uncommitted at the end of the backup.
March 16, 2010 at 2:58 am
Thank you for your question.It's a good point that I had never attended.It's obvious in question that 10000 rows are inserted without transaction because the records are inserting through a loop and any records that insert until the completion time of full backup will be included.
March 16, 2010 at 5:30 am
I'm not sure I understand the explanation though:
Whenever a full backup is initiated it will place a pointer in the transaction log in order to understand from where the backup started. So any transaction happening,after the pointer, will be included in the backup.
Is this saying that a full backup backs up everything fully before the pointer, and then pulls the data after the pointer? Otherwise, what is the point of the pointer?
If it doesn't mean that, what does it mean? Why would a pointer be placed in the transaction log on the initiation of a full backup if it is going to backup the data before AND after the pointer?
March 16, 2010 at 5:57 am
vogelz (3/16/2010)
I'm not sure I understand the explanation though:Whenever a full backup is initiated it will place a pointer in the transaction log in order to understand from where the backup started. So any transaction happening,after the pointer, will be included in the backup.
Is this saying that a full backup backs up everything fully before the pointer, and then pulls the data after the pointer? Otherwise, what is the point of the pointer?
If it doesn't mean that, what does it mean? Why would a pointer be placed in the transaction log on the initiation of a full backup if it is going to backup the data before AND after the pointer?
I'm not sure if the explanation is 100% technically accurate. My understanding (which might be wrong as well) is that the backup first copies all the data pages, then adds all the log pages starting from the start of the oldest uncommitted transaction right up until the time the backup ends.
On a restore, first all data pages are restored; then the log entries included in the backup are used to
a) roll forward any transactions that were committed while the backup was running, and
b) roll back any changes from transactions that were not yet commited when the backup finished.
The latter is required because the backed up data pages may already have been modified by those transactions.
As I said, the exact implementation might be slightly different. But the main point to remember is that the backup/restore process ensures that the restored database includes the changes from all the transactions that were finished when the backup ended, and no changes at all from any transactions that were still running at that time.
March 16, 2010 at 6:04 am
Thanks Hugo, that does make much more sense.
Andrew
March 16, 2010 at 7:00 am
Hugo, after getting the questin wrong;-) and re-reading a few times in disbelief I found on <http://msdn.microsoft.com/en-us/library/ms175477.aspx> - Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup.
So, in a full backup, all completed transactions will be added to the full dump. everything else will be added to the transaction log dumps if being performed.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
March 16, 2010 at 9:37 am
This was a good question. And thanks Hugo for the explanation.
March 16, 2010 at 9:53 am
Excellent question. As a non-DBA who uses MS SQL a lot, I'd sorta kinda knew that backups would be complete, but the question (and Hugo's expansion on the explanation) makes it clear how that's accomplished.
BTW, I was tickled that the "correct answer" section started with the same words as in the title of one of today's newsletter's featured articles, Bob Hovious's T-SQL: Why “It Depends”.
March 16, 2010 at 9:58 am
Hugo has a great explanation of what happens, and AFAIK, that's what happens.
The a) and b) points in his post are what are being handled in the error log when a database is started or restore finishes with the rollback/rollforward messages.
March 16, 2010 at 10:08 am
D'oh. I clicked the correct answer and then clicked submit. When looking up right after the submit, I realized I clicked the wrong answer unintentionally. That sucks.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 16, 2010 at 10:09 am
Great question. Would have been better if I had not missed my click.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 16, 2010 at 10:35 am
sjimmo (3/16/2010)
Hugo, after getting the questin wrong;-) and re-reading a few times in disbelief I found on <http://msdn.microsoft.com/en-us/library/ms175477.aspx> - Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup.So, in a full backup, all completed transactions will be added to the full dump. everything else will be added to the transaction log dumps if being performed.
All completed transactions are added to the dump regardless whether they were initiated before or after the dump started, so long as they were committed before the dump is completed. Looks like the note on the BOL page you mention is the key. it states that full database backups "represent the whole database at the time the backup finished". Since I like to make the short story long, here is the small test to confirm, with the only exception it does not have the while loop for the while loop is evil as the select is already a loop: I used 2 instances of the SSMS and 3 query windows. In the first window of instance 1 I started a full backup of the AdventureWorks:
use master;
go
backup database AdventureWorks
to disk = 'C:\Useless\Dump\AdventureWorks_20100316.bak'
with format, name = 'AdventureWorks Backup 20100316';
go
The above assumes that I do have C:\Useless\Dump folder already available. While the backup was still running, in the first query of the instance 2 I initiated and committed a small transaction which inserted 1000 records into a new table:
use AdventureWorks;
go
if exists
(
select 1
from sys.objects
where
[object_id] = object_id(N'[dbo].[dump_me]')
and [type] in (N'U')
)
drop table dbo.dump_me;
go
create table dbo.dump_me
(
record_id int not null,
record_desc varchar(20) not null,
last_modified datetime not null default (getDate())
);
go
-- begin and commit first transaction
-- inserting 1000 records into dump_me
-- while the backup is still running
begin tran;
with numbers (number) as
(
select top 1000 row_number() over (order by a.[object_id]) number
from sys.objects a cross join sys.objects b
)
insert into dbo.dump_me (record_id, record_desc)
select
number, 'record_' + replace(str(number, 6), ' ', '0')
from numbers;
commit tran;
select count(1) record_count
from dbo.dump_me;
go
While the backup was still running I started (in query 2 of instance 2) another transaction to insert another 1000 records into the same table but I put applied the 45 seconds brakes on this one so the backup could finish before the second transaction was committed:
-- begin second transaction which is also
-- inserting 1000 records into dump_me, but
-- make it take a very long time to execute,
-- so it is committed after the backup has
-- completed.
begin tran;
with numbers (number) as
(
select top 1000 row_number() over (order by a.[object_id]) + 1000 number
from sys.objects a cross join sys.objects b
)
insert into dbo.dump_me (record_id, record_desc)
select
number, 'record_' + replace(str(number, 6), ' ', '0')
from numbers;
waitfor delay '00:00:45'
commit tran;
select count(1) record_count
from dbo.dump_me;
go
After backup was finished and then the second transaction committed, I closed instance 2 and restored the database which now (before the restore) had my table with 2,000 records:
use master;
go
restore database AdventureWorks
from disk = 'C:\Useless\Dump\AdventureWorks_20100316.bak';
go
This action restored the datatase to the state in which it was at the time the backup was committed, meaning it already had my table with first 1,000 records because though the transaction inserting those was initiated after the backup started, it committed before the backup completed.
The moment of truth can be now checked by running this:
use AdventureWorks;
go
select count(1) record_count
from dbo.dump_me;
go
drop table dbo.dump_me;
go
and getting this as a result:
record_count
------------
1000
Oleg
Viewing 15 posts - 1 through 15 (of 65 total)
You must be logged in to reply to this topic. Login to reply