SQL Homework – September 2018 – Backup and Restore continued.

Leave a comment

September 3, 2018 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,469 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: