February 2, 2012 at 12:50 pm
I have a question about backups and where they draw the line at what data they store and restore. Lets say I kick off a full database backup at 12:00. My backup takes 30 mins.
Scenario 1:
I start a transaction that updates a bunch of data just after i kicked off the backup. the If the transaction finishes before the backup, will the changes be in my 12:00 full backup?
Scenario 2:
I start a transaction BEFORE i kick off the backup. if the transaction finishes before the backup finishes, will the changes be in the 12:00 full backup?
Im having trouble testing this as backups are taking less than a second for the small databases on my laptop.
February 2, 2012 at 12:56 pm
From Books Online, what it says is a full backup contains the data and the rest (logins, stored procs, etc) at the time the backup is finished. So any transactions that are started before or after the backup was started will be inside the full backup, if those transactions are completed before the full backup is completed..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 2, 2012 at 1:34 pm
Depends on when the transactions complete. If they commit before the data-copying portion of the backup completes, yes. Otherwise no.
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
February 5, 2012 at 12:12 pm
Bru Medishetty (2/2/2012)
From Books Online, what it says is a full backup contains the data and the rest (logins, stored procs, etc) at the time the backup is finished. So any transactions that are started before or after the backup was started will be inside the full backup, if those transactions are completed before the full backup is completed..
do you mind sharing the books online link you are qouting from here. i cannot find it and ive been through most of the articles when i search for backup in BOL. Thanks.
February 5, 2012 at 7:49 pm
winston Smith (2/5/2012)
Bru Medishetty (2/2/2012)
From Books Online, what it says is a full backup contains the data and the rest (logins, stored procs, etc) at the time the backup is finished. So any transactions that are started before or after the backup was started will be inside the full backup, if those transactions are completed before the full backup is completed..do you mind sharing the books online link you are qouting from here. i cannot find it and ive been through most of the articles when i search for backup in BOL. Thanks.
Yes sure.. In this Books Online link...
http://msdn.microsoft.com/en-us/library/ms175477.aspx
Please find under the section Database Backups. The following line
A full backup of the whole database. Database backups represent the whole database at the time the backup finished.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply