Tag: sql server

I’m writing this in support of a few talks I give that talk about backups. This is how I see things and it’s based on things I’ve learned over time. Some of this comes from a great explanation on Technet from Paul Randal, but there are misc other sources that I can’t be sure of which ones I’ve used. Long story short: I learned most of this from others and docs. It’s not all me.

There are two parts to a full backup in SQL Server:

• data reading
• log writing

Technically both sections read and write, but this is how I think of things. I’ll describe there:

Data reading – The backup process goes through all extents allocated and reads a page, sends it to a buffer and that gets written to the backup file. This happens as fast as SQL can do it, just going through the pages, but it takes most of the backup time.

Log writing – all of the log records that are written while the data reading portion of the backup is running are appended to the backup file, after the data pages.

Let’s say that the entire time of the backup, from the File Created to the File Last Modified timestamps on the backup file is represented by t. The data reading portion of the backup takes time d. The log writing portion of the backup takes time l.

This gives us:

t = d + l

My database is transactionally consistent at backup start time + d, not + t. When is that? I’m not sure, but usually it’s fairly close to the timestamp at the and of t.

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.

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.