SQL Azure Backup: What does transactionally consistent mean?

Question

I'm using redgate's sql azure backup tool: http://www.red-gate.com/products/dba/sql-azure-backup/

It looks like if you check "Make Backup Transactionally Consistent" you get charged a full day's use for sql server. I'm wondering if I need to check this.

I do daily backups to blob storage and I backup the database to my local machine to work with every 3 days or so.

If I don't check the Transactionally Consistent box, am I going to run into any problems?

Answer 1

Well as the person who wrote SQL Azure Backup at Red Gate I can say that the only way to create a guaranteed transactionally consistent backup in Azure currently is indeed to use CREATE DATABASE ... AS COPY OF. This copy only exists for the duration of us taking the backup and is then dropped immediately afterwards.

If you don't check the box you'll only hit problems if there is a risk of transactions being in an inconsistent state when reading the data from each table in turn. CREATE COPY OF can take a very long time and also may cost money for the copy too.

If you're backing up to a BLOB you're using the Microsoft Import Export service rather than SQL Compare and SQL Data Compare technology but that also reads data from the tables to could be inconsistent too.

Hope this helps Richard