Subscriber only lesson.

Sign up to the Introduction to SQL course to view this lesson.

View pricing

About this lesson

We will take a look at the master and model databases as well as the msdb and tempdb Databases

Exercise files

Download this lesson’s related exercise files.

System Databases.docx
199.4 KB System Databases - Solution.docx
199.8 KB

Quick reference

System Databases

There are four default system databases.

When to use

System databases allow you to tweak system related things.

Instructions

For the most part, especially while you've just started learning things, you can ignore the system databases.

  • The Master Database handles system related things like Logins.
  • The Model Database allows you to set initial default database models.
  • The MSDB Database handles SQL Server Services.
  • The TempDB saves temporary data that you add to a database before committing it.

Hints & tips

  • System databases let you tweak system things.
  • This is just an overview, as you can mostly ignore them at this point.
  • 00:05 In this video I wanna talk about system databases.
  • 00:08 Now this is a little bit technical, and
  • 00:10 we're just gonna gloss over it very quickly.
  • 00:12 But up until now we haven't actually created any of our own databases.
  • 00:16 We haven't built any of our own tables, we haven't put any data in, but
  • 00:19 we still have this Databases folder.
  • 00:21 So if we click the little plus button we get this Systems Databases folder and
  • 00:25 that's what I'm going to talk about in this video.
  • 00:27 It has these four different things: master, model, msdb, and temp.
  • 00:32 So what are these things and why do we care?
  • 00:35 Well these are sort of behind the scenes things that at this
  • 00:38 point you're really not gonna have to deal with.
  • 00:40 In the future if you're doing advanced things you're gonna have to
  • 00:43 know what these are and maybe make some changes and do some things with them.
  • 00:47 But for the most part you can ignore these.
  • 00:49 But I wanna spend just a minute talking about them so
  • 00:51 at least you're familiar with what in the world they are in case you ever do need to
  • 00:55 learn about them in order to use them.
  • 00:57 So we'll start out with this master.
  • 00:59 And if we just click on this.
  • 01:01 Basically the master folder, the master database, contains all of the sort of
  • 01:06 system level information for your server, for your SQL server, for your databases.
  • 01:12 I'm talking about logins and other sort of things like that.
  • 01:16 So later on we're gonna talk about backing up stuff.
  • 01:20 It's kind of a good idea to maybe backup your master database every once in a while
  • 01:24 because it has your login stuff.
  • 01:26 Now, like in the last video we saw, we're just using the basic Windows
  • 01:30 authentication so we don't really have a whole lot of stuff to do.
  • 01:33 If we click on the Security, we can see different users, and
  • 01:36 we just have the Microsoft authentication stuff, and there's a guest.
  • 01:40 So there's not a whole lot of stuff, and I'm not gonna open any of these up,
  • 01:43 cuz we don't know how to use any of this stuff yet.
  • 01:45 And it would just be too much to take at this point.
  • 01:48 Like I said, I'm just gonna sorta make you familiar in this video of
  • 01:51 where these things are and what they sort of do.
  • 01:53 So the master database, just think of it as a systems sort of thing.
  • 01:57 The model database, it's sort of used as a template.
  • 01:59 So any time you create a new database, it's gonna use this model to
  • 02:04 model that database to put the default things in.
  • 02:09 So if you're creating a lot of databases and you wannna create certain things every
  • 02:12 time, you might mess around with this model database thing right here, msdb.
  • 02:18 Msdb, it deals with different services that SQL Server uses,
  • 02:23 things like database mails, server agents, active jobs and things like that.
  • 02:27 So at this point you don't know what any of that stuff is so
  • 02:30 you can definitely ignore this one, but that's what this thing handles.
  • 02:34 And finally the tempdb.
  • 02:36 This one probably does the most of anything.
  • 02:38 It handles all temporary data.
  • 02:42 Whenever you sort of make queries to your database in the future, it brings back
  • 02:46 that information, stores it temporarily here while you decide what to do with it,
  • 02:50 things like that.
  • 02:51 So like I said, these are the system databases.
  • 02:54 You really at this point don't need to know what any of these things do,
  • 02:57 just kinda wanted to say, hey, here they are.
  • 02:59 So in the future if you ever do need to learn about them,
  • 03:02 this is where you'll deal with all that stuff.
  • 03:04 In the future, we're gonna be creating our own databases, putting in our own data, so
  • 03:08 we're not gonna deal with this system stuff at all.
  • 03:11 But just take a quick few minutes to take a look at
  • 03:13 these things just to sorta familiarize yourself.
  • 03:16 Click through here and see what all this stuff is.
  • 03:18 In the next video, we will dive in and start actually building our own database,
  • 03:23 putting in our own data.
  • 03:24 Gonna be a lot of fun, and that's all for this video.

Lesson notes are only available for subscribers.

Gift this course