Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full backup Scenario


Full backup Scenario

Author
Message
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
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”.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36083 Visits: 18738
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
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

Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3346 Visits: 2826
Oleg,

I wasn't arguing the point. I think it is a great question, causing a of of thought and good conversation. I just got it wrong, I admit it. My thinking was you needed the T-Log seperate. I also posted another location which explained the question.

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
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
sjimmo (3/16/2010)
Oleg,

I wasn't arguing the point. I think it is a great question, causing a of of thought and good conversation. I just got it wrong, I admit it. My thinking was you needed the T-Log seperate. I also posted another location which explained the question.

I know you were not Steve, and I apologize if my reply caused you to think so. I was just curious to actually check the backup behavior, so it is now tattooed in my brains.

Oleg
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1670 Visits: 459
I will disagree with the official answer because the question did not ask if the transaction was included in the backup file but rather asked if the data would exist after restore.

Because this is a full backup then the log must be restored as part of the complete database restore and the log has the transactions (no mention of point in time).

From the manual:
The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.

So if the log was restored then all the data would be present.

IMHO -- Mark D Powell --
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
Mark D Powell (3/16/2010)
I will disagree with the official answer because the question did not ask if the transaction was included in the backup file but rather asked if the data would exist after restore.

Because this is a full backup then the log must be restored as part of the complete database restore and the log has the transactions (no mention of point in time).

From the manual:
The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.

So if the log was restored then all the data would be present.

IMHO -- Mark D Powell --


I'm confused. For the data to be present after a restore, it has to be in the backup, doesn't it?

Could you explain what you are trying to say?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
I think the link http://msdn.microsoft.com/en-us/library/ms186289.aspx explains it very succinctly that 'Full database backups represent the database at the time the backup finished.'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search