System Databases

Quick reference

• 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.