SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Homework – September 2018 – Backup and Restore continued.

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...