Transactionally Consistent

What does this mean? I had someone ask me in a session recently, and I think I have a good explanation, but I thought this was worth a blog.

Transactionally Consistent means that there is a snapshot of the database at a point in time that ensures committed transactions are actually reflected in the database and uncommitted transactions are not. I haven’t found a better concise definition, but Paul Randal has a great longer explanation.

This is reflected in the ACID RDBMS model as the C for consistent.

In a SQL Server backup process, this is accounted for by including some amount of the transaction log in the full database backup. The amount included depends on your workload and the amount of data you back up, and if you read Paul’s explanation, you’ll understand more.

Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.