Printed 2014/08/02 03:35AM

Applied SQL: Demonstrate Simple Backup and Restore

By Jen McCown, 2011/10/26

Let’s apply a little backup and restore knowledge, eh wot?

I introduce this whole concept in Applied SQL: You Have Homework. Find all assignments on the Applied SQL page.

Prerequisites: basic T-SQL, create objects/data, basic understanding of recovery models and backup types.

Reading – SQL Server Books Online articles:

Setup: We’re going to build a script that demonstrates different backups, and the effect of their restores.  The setup and assignments should all contribute to this one script, so save your work!  First, script a new test database and table simply:

Level 1 Assignments: Backups – after the Setup section in your script, add the following:

[1-1] Full backup 

[1-2] TLOG backup

[1-3] DIFF backup, one more TLOG backup

For the purposes of this exercise, we’re naming the backup files a little oddly (full, log1, diff2, log3). We’re doing this to help us keep track in the next assignment section – restores.

Setup2: Speaking of the next section…we have the option of using the same backup files, or of rerunning then entire script. Rerunning the whole script is kind of fun, so go back to all of your BACKUP statements and add options to initialize and format your backup file.

Level 2 Assignment: Restores – after the Backups section in your script, add and run a “drop database; GO ” to make way for the restores, then add the following:

[2-1] Restore FULL

[2-2] Restore FULL, DIFF

[2-3] Restore FULL, DIFF, TLOG

Bonus points: Post your scripts (or a link to them) in the comments below, just to show off that you did it.

Double bonus points: Do you think we should’ve done something more here? Post a related assignment of your own in the comments, and I’ll see how I do.

Recommended optional videos, by MidnightDBA:

 -Jen McCown

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.