Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
1

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?

share|improve this question

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
This is a bit misleading. There is no such thing as marked as rolled back within the backup. Log generated during data backup is copied out into the backup along with the data, but the business of deciding what to rollback is left out to the recovery process after RESTORE. Eg. If the an additional LOG backup is taken and then restored along with the full backup, some of those transactions will be committed, not rolled back. Is not the BACKUP that 'marks' them to be rolled back, is the recovery process that decides so. – Remus Rusanu Mar 5 '12 at 15:28
True. Sorry about messing the line between what is in the recovery and backup process. In the end though, the result is the same. – Grant Fritchey Mar 5 '12 at 17:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.