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.

Exam 70-432: Week 1 - The Last Lab Exercises

Finally! The last batch of Lab Exercises, from Chapter 5, Lesson 3:

Practice 1 Populate a Thesaurus

Step 1:

select ProductDescriptionID,Description
from Production.ProductDescription
    where CONTAINS(Description,N' FORMSOF (THESAURUS,metal) ')

ProductDescriptionID Description
-------------------- -----------

(0 row(s) affected)

Steps 2-3:

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
    <diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            _metal
            _steel
            _aluminum
            _alloy
        </expansion>
    </thesaurus>
</XML>

Step 4:

use AdventureWorks
go
exec sys.sp_fulltext_load_thesaurus_file 1033;
go

Command(s) completed successfully.

Step 5:



Practice 2 Build a Stop List

Step 1:



 Step 2:

create fulltext stoplist ProductStopList;
go

Command(s) completed successfully.

Step 3:

alter fulltext stoplist ProductStopList ADD 'bike' language 1033;
go

Command(s) completed successfully.

Step 4:

alter fulltext index on Production.ProductDescription
    set stoplist ProductStopList
go

Command(s) completed successfully.

Step 5:

Exam 70-432: Week 1 - Even MORE Lab Exercises!

Here's the next batch of Lab Exercises. These are for Chapter 5, Lesson 2 in the Lab Manual:

Step 1:



Step 2:



Step 3:

 

Step 4:

 

 Step 5:



Step 6:

 

Step 7:

 

 Step 8:



Step 9: