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:

• Backup (Transact-SQL)
• Restore (Transact-SQL) 
• ALTER DATABASE SET options (for FULL mode)
• Choosing the Recovery Model for a Database
• Backup Under the Full Recovery Model

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:

• Name the database “testing”
• Set it to FULL recovery mode.
• Create a simple table (T1) in it with at least one varchar(100) column.
• Insert a few rows into your table.
• Add a “drop database; GO ” at the beginning of your script, so you’ll have a clean start each time.

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

[1-1] Full backup 

• Backup the database to disk, filename testing.bak
• Insert a few more rows into your table.

[1-2] TLOG backup

• Backup the LOG to disk, filename testingLOG1.bak
• Insert a few more rows into your table.

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

• Take a DIFFERENTIAL backup of the database, filename testingDIFF2.bak
• Insert a few more rows into your table.
• Backup the LOG to disk, filename testingLOG3.bak
• Insert a few more rows into your table.

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

• Restore the FULL backup of the database (from step 1-1 above).
• Select all data from your table; note how data entered later (after the FULL backup) isn’t here. Exactly as we planned it…
• Drop the database & comment out this section when you’re ready to move on.

[2-2] Restore FULL, DIFF

• Restore the FULL and DIFFERENTIAL backups of the database (from steps 1-1 and 1-3 above). If you haev problems restoring the differential, pay attention to the error you get.
• Select all data from your table; note how data entered later (after the DIFF backup) isn’t here. Get the tlog1/diff relationship straight in your head…do you know why we skipped right over the tlog1 backup?
• Drop the database & comment out this section when you’re ready to move on.

[2-3] Restore FULL, DIFF, TLOG

• Restore the FULL, DIFFERENTIAL, and TLOG3 backups of the database (from steps 1-1, 1-3, 1-4 above). Again, pay attention to any errors you get to figure out the problem.
• Select all data from your table to see where you stand.

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:

• Basic Backup
• Restore With Move
• Backup With Init
• Backup File Extensions
• See also Best Backup Class Evar series on the Admin page.

 -Jen McCown
www.MidnightDBA.com/Jen