Tag: Backup/Recovery

I learn new things all the time. This was one that actually stunned me. Huge props to Gail Shaw for posting a note about this in a thread.

Run this code:

CREATE DATABASE MyRestoreTest
;
GO
USE MyRestoreTest
go
BACKUP DATABASE MyRestoreTest TO DISK ='myrestoretest.bak';
GO
CREATE TABLE mytable( id INT)
;
GO
USE master
go
BACKUP LOG myrestoretest TO DISK = 'myrestoretest_log.trn' WITH norecovery

You’ll see this in your Object Explorer

Ugh.

I haven’t started a restore. I’ve run a backup. Apparently this causes problems, as noted by Gail in the thread. Sure enough, it’s documented in the BACKUP command, in the Log-specific Options.

I had never scheduled backups with this type of option, but you might have a job that does this if you were preparing for a failover. Having a script ready it a good idea, but if it executes unexpectedly, this could happen.

The lesson: make sure you know the options when you run a command. Always test, and if something strange happens, search or ask what might have happened.

I wrote about transactional consistency recently. Why do we need this?

Imagine that I have an orders table and an ordersdetail table in my database. The orders table has the order header (date, customer, etc) and the detail table includes information about the items purchased (product, qty, price). Let’s say my orders table has 10 orders and my detail table has 5 items for each order for a total of 50 rows in the detail table.

If I want a backup that is consistent, I need to have all of the orders and details included in the backup. I don’t want partial orders, so all my inserts for orders and details are included in a transaction. That means my backup needs to contain a complete, new order, or no new order at all. Anything else wouldn’t be consistent.

The backup process you choose needs to account for this. It cannot include partial orders from a transaction inside of it. Could that happen?

Sure, and let’s see how. Backups proceed linearly and it takes time for them to complete. Imagine that I have a process that starts backing up my Orders table. It reads orders 1 and 2.

While this is happening, someone inserts a new order, #6, into the system. If my backup system is reading pages, it might continue through orders 3-6 and then start on details. Imagine now the user rolls back the new order. When my backup completes, it will include (and restore) an order #6. That could be a problem.

The other problem could be that the backup process gets to the details table and starts backing up the first 5 details from order#1. While this is happening, a user inserts a new order and order details in a transaction (order #7), and it completes. The backup process moves through the details table and gets all the details, including the new detail records inserted during the backup. However the orders table has already been backed up. The restore will include details for order #7, but not the header (Orders table).

That’s bad.

Don’t use file backups, no open file managers, no exports, no BACPACs as backups for your data. You might have serious data issues on restore, which get worse as your workload is busier on the system.

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.

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.