Tuesday, May 29, 2012

Exam 70-432: Week 2 - QotD

This week's Question of the Day comes from SQLServerCentral.com, and asks: which system databases do not allow differential backups, but do allow full backups?

I thought the answer would be the master database. This question was pretty easy to check out:

backup database master to disk=N'C:\Test\master.back'

Processed 376 pages for database 'master', file 'master' on file 1.
Processed 3 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 379 pages in 0.614 seconds (4.816 MB/sec).

backup database master to disk=N'C:\Test\master.back' with differential

Msg 3024, Level 16, State 0, Line 1
You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


Just as I thought!

For completeness, we'll verify the other system databases:

backup database model to disk=N'C:\Test\model.back'

Processed 168 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 170 pages in 0.338 seconds (3.909 MB/sec).

backup database model to disk=N'C:\Test\model.back' with differential

Processed 32 pages for database 'model', file 'modeldev' on file 2.
Processed 1 pages for database 'model', file 'modellog' on file 2.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 33 pages in 0.195 seconds (1.319 MB/sec).

 
No problem with the model database. Up next is msdb:

backup database msdb to disk=N'C:\Test\msdb.back'

Processed 2824 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 5 pages for database 'msdb', file 'MSDBLog' on file 1.
BACKUP DATABASE successfully processed 2829 pages in 1.066 seconds (20.726 MB/sec).

backup database msdb to disk=N'C:\Test\msdb.back' with differential

Processed 104 pages for database 'msdb', file 'MSDBData' on file 2.
Processed 5 pages for database 'msdb', file 'MSDBLog' on file 2.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 109 pages in 0.347 seconds (2.439 MB/sec).


OK, msdb can be backed up differentially.


Finally, we have tempdb. The tempdb database gets re-created whenever SQL Server starts up, so there's no need to back it up at all; but just because it's unnecessary, does that mean it's impossible?

backup database tempdb to disk=N'C:\Test\tempdb.back'

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


Yes it does!

So, if you can't perform a full backup, you surely won't be able to back it up differentially, right?

backup database tempdb to disk=N'C:\Test\tempdb.back' with differential

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


That makes sense.

So there you have this week's Question of the Day: the master database can be backed up using a full backup, but not a differential backup.

4 comments:

  1. Out of curiosity: what is the setup you're using for your self-training? I have no experience with mirroring or replication (we haven't done these we're I've worked) and I feel like I should have some when I sit down for the exam, so I am setting this up at home. Since I have the use of one computer with the horsepower for these apps, I'll be attempting mirroring & replication using SQL2008 installed on a Windows 2008 VM, SQL2008 (Eval edition) on the host Windows 7 (64-bit/4MB RAM) system, and the Witness on a SQL 2008 R2 Express also on Windows 7. Are you doing something similar & how is it working for you?

    ReplyDelete
  2. High Availability is definitely one of the skills measured in 70-432, so you're smart to get some experience with it.

    I have a similar setup as you: a Dell Inspiron 9400 with a T5600 CPU and 4GB RAM, running Windows 7 64-bit. My VM's are all Windows Server 2008 R2 with SQL Server 2008 R2. I can run three VM's simultaneously (albeit slowly), so I'm not anticipating any problems with HA.

    High Availablity is scheduled for early to mid August, so I haven't done anything with it yet.

    Stay tuned, and consider blogging about your experiences.

    Thanks for the comment.

    ReplyDelete
  3. GEEZE! studied, memorized and scored a 92% three times on Mike Hotek's, "Self Paced 70-432 Training Kit" before taking the 70-432 exam and I failed it. I feel I wasted my Time and Money purchasing this Training Kit as only 3 or 5 questions I studied for were in the Book and those were re-worded so differently I barely reconized them. So much for Mr. Hotek's "This test resembles the real test" remark???? What a sham!

    ReplyDelete
  4. Hi jeff,

    I don't think you wasted your money on Mike Hotek's book. Lots of people have used it to pass the 70-432 exam.

    First, make sure you check out the errata at http://support.microsoft.com/kb/968609.

    'Resembling the real test' means that if you take the practice test, you'll get a sense of what the real test is like. Giving you the exact questions is what a braindump does. Don't use braindumps. Period.

    Keep in mind that the book gives you the information you need to pass the 70-432 exam; it doesn't teach you how to be a DBA.

    As Mike says in the Introduction:

    "It’s assumed that before using this training kit, you already have a working knowledge of Microsoft Windows and SQL Server 2008, and you have experience with SQL Server or another database platform."

    A different approach may help. As I said here, certification should be a by-product of your studying, not the goal.

    I don't suggest you try to memorize the material. I read a really good quote from Ellen Langer's book The Power Of Mindful Learning which said that memorization is trying to learn something that isn't relevant to you.

    So how do you make this stuff relevant, and therefore easier to learn? First, set up a home lab, with Windows Server and SQL Server installed. Use VM's, they'll make your life a lot easier.

    Then find a project that interests you. For example, if you like baseball, download some baseball data. Design a database and load the data. Back it up, destroy it, restore it. Optimize it, mirror it, replicate it. Write queries against it and trace the execution plans. You can use my syllabus if you want.

    This stuff isn't easy, but quoting Jimmy Dugan from A League of Their Own:

    "It's supposed to be hard. If it wasn't hard, everyone would do it. The hard... is what makes it great."

    Keep at it, and good luck. Come back from time to time and let us know how you're doing, or consider blogging about your experiences.

    Thanks for the comment.

    ReplyDelete