SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Full backup Internals


Full backup Internals

Author
Message
anil702
anil702
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 729
Hi folks,


I need some clarification related on FULL Backup.

Scenario - Some one exec SP which contains 100 insert/updated statements 10:45 Am.

Now i want take backup of that DB 10.46 AM but if i have taken the backup 100 inserted/updated modifications will be available in backup file ..?

If Available how many transaction will be there in backup file.
If not how can we force the backup command after the completion of SP(consider may be some huge transactions)

General points (Don't consider Checkpoint/Lazy writer)

After exec Full backup command what happens internally..?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87765 Visits: 45272
The backup started after the commands started, so as long as they finish before the backup finishes, they will be in the restored database

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


baabhu
baabhu
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: 1673 Visits: 1218
GilaMonster (7/10/2012)
The backup started after the commands started, so as long as they finish before the backup finishes, they will be in the restored database


Basically the message we see in the error log after the DB is restored on a difference machine are those commands who have started after the backup started. Commands finished before the backups are committed and commands don't finish are rolled backup.

Please correct me if my understanding is correct.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87765 Visits: 45272
Not entirely.

What messages?

The backup copies the data pages as it encounters them. It also copies the portion of the tran log it needs to make the restore consistent.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


anil702
anil702
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 729
but if we consider
DB - 200GB
for backup it will take 25 min..
suppose 10:45 i started backup of 200GB database
Small Transactions occurring on particular database between 10:45 to 11:05 all are committed transactions .
Backup will cmp at 11:10.
Now we restore the backup file can i get data up to 11:05 (or) 10:45
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87765 Visits: 45272
You will get a database that is transactionally consistent as of the point that the backup completed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


anil702
anil702
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 729
Means if backup takes two hours and transactions are committed up to last min
like
12 clock backup started and end at 2 clock i will get data up to last min which are committed.
between 1:50 to 1:59 (committed Transactions)


If the above statement is True
Thanks Gail...
rajeshjaiswalraj
rajeshjaiswalraj
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 405
10:45 you started your sp for inserting and if your insert completed at 10.46 and then you started, Full backup - Backup file having all Transaction.

10:45 you started insert and its taken 10.50 for Insert your backup started at 10.46 and its completed at 10.49 - Yourbackup file having all transaction

10:45 you started insert and its taken 10.50 ,backup started at 10.46 and its completed at 10.50 - Your backup file dont have last one min of data

Note : Its not depand at what time you startd insert - Its depand on backup duration. how much time you backup will run ,at that duration how many transation has happend its contain all.

One more very Imp thing what you inserted (commited or uncommited ) - You are doing same above process but what you have inserted is uncommited. Then also your backup file having all transaction but whan you will do restore, SQL server will do recovery and all uncommited data will be rollforward
and you will lose all uncommited data.


You can msg me - rajeshjaiswalraj@gmail.com
w00t
Chris_M
Chris_M
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 1948
Hi Gail,

"The backup started after the commands started, so as long as they finish before the backup finishes, they will be in the restored database" sort-of suggests that the start-time of the transaction vs start-time of the backup dictates whether the transaction will be included.

My understanding was that, independent of when a transaction starts, it will be included in the restored database if it completes before the backup completes i.e. transaction COMMIT is determinant (and transaction BEGIN is irrelevant).

Example reference
"Use a Full Database Backup to Restore the Database
You can re-create a whole database in one step by restoring the database from a full database backup to any location. Enough of the transaction log is included in the backup to let you recover the database to the time when the backup finished. The restored database matches the state of the original database when the database backup finished, minus any uncommitted transactions." (emphasis added)


Chris
Ravid_ds
Ravid_ds
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 550
Good read to clarify your doubt
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
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