In the very first SQL Homework post you were asked to take a backup. In fact it asked you to both take a backup and restore it. Because, I’ll be honest with you, if you can’t restore a backup then you might as well not have taken it. That said, it’s probably one of the most important parts of our job. So this month, we are going back to backups (and restores) and adding a fair amount of complexity to the whole thing.

Your tasks (make sure you read and understand everything before you start, there are a few optional bits):

• Take a full backup of a database.
• This is a large database, so to help with the speed, split the backup into three seperate files. (opt)
• Your backup is still taking longer than you’d like so play with the BUFFERCOUNT and MAXTRANSFERSIZE settings. (opt)
• Make some changes to the database. Add a table, add some rows to a table, whatever.
• Take a differential backup of the same database.
• You are a bit worried about this backup so back it up to three seperate locations. Use only one command. (opt)
• Make some more changes to the database. Add a table, add some rows to a table, whatever.
• Take a log backup.
• Make some more changes to the database. Add a table, add some rows to a table, whatever.
• Restore your initial full backup. Make sure that the database is in a state where it is both readable and you can continue to do restores.
• Did you remember to take a tail of the log backup?
• Check that you can read from your database, and that the changes you’ve made in the process of this homework aren’t there yet.
• Restore your differential backup. Again, you need to make sure that the database is in a state where it is both readable and you can continue to do restores.
• Check and make sure that the database is in it’s expected state. i.e. the changes you made between the full and the differential backups are there now.
• Perform the same steps for the first log and then the tail of the log.
• Without restoring any new backups mark the database as restored.

This may sound like a fair amount of work but it really isn’t all that bad. I’ve seen 90% of this done as a demo in a presentation before. The whole thing shouldn’t take all that long. Yes, there some curve balls in here but it’s important to know what’s possible and to at least give it a shot in case it comes up later.