Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Full backup Scenario Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 9:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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”.
Post #883932
Posted Tuesday, March 16, 2010 9:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #883940
Posted Tuesday, March 16, 2010 10:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
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
Post #883951
Posted Tuesday, March 16, 2010 10:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
Great question. Would have been better if I had not missed my click.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #883953
Posted Tuesday, March 16, 2010 10:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,676, Visits: 1,758
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
Post #883978
Posted Tuesday, March 16, 2010 10:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 2,917, Visits: 2,532
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
Post #883999
Posted Tuesday, March 16, 2010 11:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,676, Visits: 1,758
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
Post #884008
Posted Tuesday, March 16, 2010 11:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:00 PM
Points: 1,384, Visits: 402
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 --
Post #884012
Posted Tuesday, March 16, 2010 11:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:02 PM
Points: 20,734, Visits: 32,515
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?



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)
Post #884021
Posted Tuesday, March 16, 2010 11:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:48 AM
Points: 3,965, Visits: 3,644
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.'

Post #884034
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse