patternsqlModerate
When you back up a database and scripts are running at the same time, at what point in time does the backup reflect?
Viewed 0 times
samescriptsthereflectyouwhatarepointtimedatabase
Problem
If I run a SQL Server database backup via Management Studio and there are scripts running at the same time, does the backup reflect the point in time when the backup was started or when it ended? My db is about 10GB so the backup takes some time, meaning there's lots of things that could go on in the meantime.
I'm using SQL Server 2008 R2
I just posted this question in SO but think this is probably a better place for it?
I'm using SQL Server 2008 R2
I just posted this question in SO but think this is probably a better place for it?
Solution
Assuming we're talking about a full or differential backup, a marker is placed at the start of the backup process. At the end of the backup process, any transactions that committed during the process are rolled forward into the backup. Any transactions that are not completed are marked as rolled back within the backup. So, the short answer to your question is, all completed transactions from the beginning to the end of the backup process. During a restore operation, the final step is the cleanup of these transactions.
Context
StackExchange Database Administrators Q#14381, answer score: 10
Revisions (0)
No revisions yet.