SQL Server 2016 Part 3 - System Databases, Backing Up and Restoring

welcome to part through this series on sequel server 2016 this video is going to talk about the system databases as part of sequel server and how you can backup and restore them so the video is all about the four main sequel server system databases we'll start by showing you how you can view those in sequel server management studio and start by looking at the master database which is the most important one at the for really importantly we'll explain how you can backup and restore the master database and that principle goes along for all the other databases as well except for one of them we'll then move through the other for the three databases the model database ms DB and temp DB next thing each one's for and a couple of the interesting things you can do with each one so important information here if not the most exciting video in the world let's get started to get started I'm going to open up sequel server management studio and to do that I'm going to use the shortcut I place in the Start menu in the previous video in the series so in the sequel server 2016 group I'm going to click the sequel server management studio shortcut when the application launches and by the way I'm assuming that you've already checked using sequel server configuration manager that your sequel server services are running and as long as we are when this when the application opens we're going to connect to this instance of sequel server that we installed in the first video in the series so that sequel 2016 training I click the connect button all of the objects belonging to that instance of sequel server will appear in the object Explorer window now whenever you install a new instance of sequel server certain system databases and objects get installed for you automatically if you want to see what system databases have been created you can expand the databases folder followed by the system databases folder and there were at least four items in there master model ms TB and temp DB the rest of this video is going to be devoted to explaining a little bit about what these four different databases are for now the intention of the video isn't to train you up to be a sequel server system administrator I'm not going to go into anywhere near enough detail to train you up for that particular role what this is designed to do is just give you a bit of a feel for what the four databases are for mainly to give you a bit of confidence in working in sequel server management studio to make sure you're not going to adversely affect anything but it's also just nice to know what these items are for even if you don't find yourself using them in the real world after this point at least you'll know what they're there for and why they in the first place let's start with the master database which is the most important of the four in the system databases folder the master database contains all the system levels information for the instance of sequel server that you're working in so for example it contains things like logon account information system configuration information and various other things as well it also contains information about the various other databases that are installed on this server so in later videos in the series when we start creating our own databases the master database will contain information about those where they're stored and various other bits of information as well perhaps most importantly of all the master database contains initialization information for the instance of sequel server so if the master database wasn't available for some reason either it wasn't there or it's becoming corrupted perhaps then your instance of sequel server can't even start because the master database is so important any good system administrator will have a regular schedule of backups to the master to make sure that if things go wrong they've got a way to restore the master to its original state now again although I'm not intended to train you to be a sequel server system administrator it will help to have a backup the master database just in case things go wrong with your particular instance of sequel server so the next section of the videos can explain how you can first of all create a backup of the master database which is pretty straightforward and then somewhat more complicated like how you can restore that backup to get back to the original state backing up the master database is the easy part to get started you can right-click on the master database choose tasks and then choose backup and that launched the backup configuration dialog box there are quite a lot of options on here I'm not going to go into every single one in detail just enough to give you the basics to get you to the point where you've got a backup file created so of course we're going to backup the master database and we're going to create a full backup and we need to choose a location to place the back of him now even if you've never created a backup before there should be at least one default location stored in this list already in a production environment it would make sense to create multiple backups to multiple different locations just for redundancy we're not going to add any more locations here I think the default location is absolutely fine but do feel free to add further locations if you wish or even just change the default one you can remove the default location and add a slightly simpler easier folder path to add to remember what I'm gonna do is I'm gonna use my default location what I would like to be able to do is show you what it what's stored in that folder at this point so I don't think I'm like looking copy this folder path at this point what I'm gonna do is I'm gonna click the Add button not intending to add a new location and it's gonna use it so I can copy this directory path that are known I can choose copyright by right clicking and copying and then choose cancel and then I'm gonna open up a Windows Explorer window by pressing windows e on my keyboard and then in the title bar the windows explore the window and you're gonna paste in the path that I've copied I hit enter then it'll browse me to that backup folder all I'm going to do at that point is head back to the backup database dialog box and then I'm going to click the ok button I know what to see after a short little progress circle when I click ok that I've got a master dot beat aka a backup file stored in that backup folder so that was the easy part restoring the master database is much more complicated I'm just going to leave this Windows Explorer window open and then head back to Microsoft sequel 7 management studio now it does appear as though this is a very there's a relatively easy way to restore the master database if you right-click master and then choose tasks you'll see there's a restore option and I can choose restore database and then I'll get a restore database dialog box so basically all I need to do here ordinarily is choose the location that I'm restoring my database from so I've restored my backup file on a device rather than in another database so the source for my backup is in another device by choose device I can then browse to the folder containing that backup file and I can click little browse button a little ellipsis button over here and when I finally launch the dialog box I can choose a location to backup from by clicking the Add button and it should browse me to the default backup location automatically and there's my master table back file so I'd double click on this file to select it and then click OK I can then attempt to click the ok button to try to backup the master database but at that point everything will go wrong you'll see these little red circles and like crosses indicating that something's gone wrong by cooking this at all I'll link at the bottom left-hand corner it'll give me a little bit of information about why this has happened so the pertinent bit of information here is that the server must be running in single user mode now that's not the easiest thing in the world to do there's no simple little right click menu used to run a server in single user mode so the next part of the video is I'm going to show you quickly how you can start the server in single user mode and then use another utility to perform the backup I'm just going to click OK at this point and then cancel out the restore dialog box and then get into the sort of nitty-gritty of restoring the master database what we'll do first of all is closed down sequel server management studio to disconnect from this instance of single server if you prefer you can disconnect from the server first and then closed our management studio but it doesn't really matter the same thing will happen I'm just going to restore down my Windows Explorer window as well and then what I'm going to do is head into the Start menu and I'm going to choose to open up a sequel server configuration manager now this is a tool we looked at in the previous video for starting and stopping sequel server services so if I click that shortcut in the style menu and click the yes button it'll launch sequel server configuration manager I need to make sure that I'm on the sequel server services page and the next thing I'm going to do is modify the way the sequel 2016 training database engine service works I'm going to right click on that service and then choose to view its properties and on the dialog box that pops up I'm going to open up to the startup parameters page now this allows me to configure exactly how the instance of sequel server will start there's a really simple promise where I can add to the startup list to make sure that the service runs in single user mode which is there which we what we're trying to do here so what's happening a - Emma so it's - M if I don't have even made that up it's quite a small font size so it's just a - like a minus symbol followed by the letter M and they need to add that parameter to the existing list and then I can click OK to add that starter parameter it'll warm you that I can't that this hasn't taken effect yet it won't take effect until I restart the service so I click OK at that point I can then right-click on that sequel server instance again and then choose restart and if the service will be stopped first let's take a little while to this couple of seconds and then what it's stopped it should automatically restart as well we'll see that happening just a moment there we go so Gary's starting the service and this instance of sequel server sequel 2016 training is now running in single user mode now that we've done that the next stage involves writing the sequel command and executing it to restore the master database now there are several places you can write this command I know some people prefer using the command line utilities called SQL CMD sequel command I'm going to do this back in sequel server management studio just because we get a little bit of actor to help in terms of syntax checking when we write this code so if I go back to the start menu and choose sequel server management studio what I'm not going to do this time is choose to connect to any database engine or any server at all when the dialog box connected to server appears until going to choose cancel what I'm gonna do is choose to create a new query and if I click the new query button I'll press ctrl and down on the keyboard we'll be doing this a lot more in later parts of this series we're not going to write too much code here right now I'm going to choose new query and I'm going to choose to connect to the database engine that we've just restarted in single user mode so I choose connect that will open up the query connected to that server the important thing that here is that we've only got one single connection remember this is meant to be open in single user mode I've got one single connection to that server now as I said we're going to be using this query view quite a lot in later parts of this series to write code to do various things with our sequel server one thing we need to in this particular instance is change the mode that the query is being entered in so if I head up to the query menu in the toolbar there's an option down here this has sequel command mode so this is the same as the name of the little command-line utility you could alternatively use to do this so sequel command mode if I choose that option that just changes the way the add the query will be executed now what I need to do I'm just going to zoom in here so you can see what I'm about to type if I hold down the control key on the keyboard and roll the mouse wheel forwards you'll be able to read a little more clearly when I'm about to write in now we can write a command that will actually restore the master database now we're going to go into a lot more detail about how to write code in sequel server in later parts of this series for now just accept that the command we're going to type in is called a restore database is a fairly sensibly named command we then have to state the name of the database that we want to restore which in this case is called master and then we have to say where the database is being restored from so in this case we're gonna say from disk and then say equals and then we need to pass in the file path and sorry the folder path and file name of the backup file now fortunately for me I kept open my backup folder I kept in Windows Explorer so I don't have to type in the entire thing I can just go back to that window click in the title bar and then just copy the directory structure from the address bar if I then go back to my query in sequel server management studio I need to type the path in or paste the path in inside a set of single quotes if I open some single quotes and then hit control V on the keyboard to paste that in I also need to put tag on to the end of this the name of the file so if you remember it's called master dr. bak so if I type in another backslash and there's a master dots and be a K I'm just gonna close down or in fact I'll close that my properties window at this point just to give myself a little bit more space and be zoom out a little bit so you can see a little more clearly the full command here and then once I've tied in the full path I can close that with close a single quote and finally right at the very end I'm gonna say with replace so that will replace the existing copy of the master database I'll finish the statement with a semicolon although that's not necessary in in Microsoft TC Court but it's ER it's a convention in standard sequel again we'll come on to that in a bit more detail when we talk about writing sequel code so there's the full statement if I can just a zoom out a little bit more maybe just auto hide the object Explorer so you can see this entire statement in one single line then nuts what we're about to do the final step then is simply to execute the statement we've written and to do that all you need to do is click the execute button or press the f5 key on your keyboard and you should get a little results panel down at the bottom of the screen hopefully telling you that the master database has been successfully restored if it gives you another another message like maybe put some kind of error message me the first thing to check I guess is the file and folder path that you've entered here make sure you spell everything correctly make sure all the punctuation is in the right place other than that I'm not sure what that suggests but hopefully having done all that you've now got a successfully restored a copy of your master database so what I'm going to do at this point is close down this query I'm not going to bother saving any changes to it if I'm asked so I can click no on the dialog box it pops up and then I'm going to close down sequel server management studio altogether and the last step really is to make sure that we've restarted our sequel server service in non single user mode in multi-user mode oh yes so let's go back to sequel server configuration manager and we can right click on our sequel 2016 training instance and choose properties head back to the start of parameters page choose the parameter we entered in early on - m and G to remove it and then click OK and once again we'll be warned that the service must be restarted for that to take effect who's actually stopped it for us automatically in this case which is quite nice so we can then right click on that and choose to start when the service has started and we'll get there eventually we can finally go back to sequel server management studio so very open management studio this time I'm going to choose to connect to my sequel 2016 training instance and if I pin the object Explorer back in place we'll see we've got all the standard system databases back in place with a nicely restored master database so that's the basics of how to backup and restore the master database should things go wrong with it hopefully in such a simple tutorial as one we're gonna follow for the rest of this series nothing can or will go wrong with the master database but at least you've now got a bit of a safety net in case things do now it's quite possible of course that the master database becomes so badly damaged or or corrupt in some other way that you can't even connect the instance of sequel server you need to connect to to run the restore command so if that happens really the only recourse you've got is to rebuild or repair your sequel server installation without getting into command-line utilities and running command line commands then the simplest way you can use to do that is running the sequel server installation utility so getting back from the very first video in this series we had a look at how you can run the sequel server installation utility to to install sequel server if I head into the Start menu and scroll down to the Microsoft sequel server 2016 folder and then in there I can look for the sequel server 2016 installations center two different versions of that 32-bit and 64-bit edition I'm going to run the 64-bit Edition because I'm running on Windows 10 64 bit edition and then if I click yes to allow the app to make changes to my machine and then when the application does load you've got a maintenance page on the left-hand side if you click maintenance then you can click the repair option to launch a wizard that will help you to repair the corrupted sequel server 2016 installation as the text says up there literally so that if things go so badly wrong that you can't even run the restore database command this is the next best thing to do I'm just going to close down that utility and then we can get on and have a look at what some of these other databases are for after the master database the model database is relatively simple to describe model is basically the templates for any new database that you create on this instance of sequel server so basically whatever objects belong to the model database whatever settings are applied to it will all be copied across into any new database you create now there are all sorts of potentially useful things you can do here so for instance you can create tables and views you can add store procedures and user-defined functions in the programmability folder you can add log ons and users and roles and schemas as part of the security features of the database and all those things will be added into nej basically create just to demonstrate the basic principles of that I'm going to create a really simple table and again we're going to go into a lot more detail about creating both databases and tables in the next few parts of the series but just to start with if I expand the tables folder you'll see that it's only got a couple of empty system folders system tables and external tables what I'm going to do here is I'm going to right click on the tables folder and choose a new table that will give me a basic table designer tool I'm not gonna go into too much detail here I'm just going to create a really boring column called test column and I'm gonna get a basic data type which again I'm gonna go into a lot more detail in different parts of this series I'm gonna give it a date type of int and then I'm gonna choose to close the table down and choose yes to save the changes to it I'll give it a really boring boring name as well test table and then if I click OK I will have added a new table to the model database tables folder if that doesn't appear by default it's just because this listed rubbish it refreshing itself so I can refresh it myself manually in a couple of ways I can right-click on the folder and choose refresh I can also highlight the folder and click the refresh button at the top of the window and that should then show me that I've got this test table in the model database as well as adding objects to the model database you can also modify some of these settings so if I right click on the database itself I can choose properties and that will give me a fairly complex dialog box with lots and lots of options I can change the main set of options are actually on the options page of the dialog box hopefully as you'd expect and it contains things such as the collation model that's used so if you remember when we installed sequel server we chose a default collation setting this includes things like what sort of characters you're using so the latin-1 character set here whether its case is sensitive or case insensitive as it is here ci whether it's access accent sensitive sorry or accent insensitive so it's accent sensitive in this case since it was sorting and whether capital letters are considered to be more importance than lowercase letters and so on and so on so if I wanted to change those at this point I can click on the drop down list and choose from a variety of other settings here as well I can choose berries or the properties I'm not gonna go into any detail here just to show you just give you an idea of how many things you can change how many things you can affect with this dialog box so I'm just gonna cancel the the properties dialog box I'm not gonna change any of those settings having created this basic test table now let's have a look at what happens when we generated a new database on this server will cover creating databases in a bit more detail in later parts of this series but just to give you the basics if you right click the databases folder you can choose to create a new database and then you'll get a dialog box to fill in all we really need to do at this stage is give the database a sensible name I'll give it a really boring name let's call it test DB show database of course at that point I can click OK and then my new database should be added to the databases folder sitting right down here at the bottom of the list just to prove that our test table has been included there if I expand the test DB database expand the tables folder we'll find that it's got a test table as well because it's inherited it from the model database so that's the basic principle of what the model database this is much simpler to understand than the master database I guess and much simpler to manipulate as well so let's just get rid of this test DB database by right-clicking it and choosing delete and then choose ok to remove that database from the server I also want to get rid of the test table from the model database so I'm going to right-click that table and I'm going to choose to delete that as well and then confirm that I do want to do that and now that model database is back to its original state just again to reiterate the importance of backing up databases if you're likely to make lots of changes to the model database to add a lots of items to it and it's certainly worthwhile backing it up so you can restore it should things go wrong so again backing up the model database is just like backing up the master database if I right click model choose tasks choose backup and then from the dialog box I'm gonna backup model database with a full backup I'm gonna place it in its default location which is already set for me so at that point I can simply click the ok button to create the backup file if I click OK and then just browse back to the window that's flora when do I left open I've now got a model back file as well restoring the model backup file is just the same as restoring the master backup file so I'm not gonna go through that long rigorous process again hopefully you can just go back to the previous part of the video to watch that part if you need to I need to do that for the model database as well next we've got the MSD B database and this database has got a couple of important jobs first of all it's tied in with the sequel server agent service which obviously we're not currently using but the sequel server agent is used to shut your regular jobs so for instance regular backups or regular data imports or exports you can set up a sequel server agent job to carry that out and the MS DB database keeps track of all that it also keeps track of all the backups and restores that we've performed in the instance of sequel server we're using such as have a quick look at what's in the MSD Gate DB database and to compare it with the model master databases so far if I expand the master database and then look in the tables folder and then system tables there's only a few basic system tables listed in there if we go to the model database as we've just seen the tables folder is essentially empty there's nothing specific to the model database just being a basic sort of empty template to begin with if you look at the MS DB database though and look in the tables folder and then the system tables folder in there there's all sorts of weird and wonderful looking bits of information stored in MS DB so as I said one thing that the MS DB database does is keeps track of all the backups and restores that we've performed there's a table here called backup set and if I would like click on that's a table and I want to see the information stored in it I can choose the option that says select top 1000 rows now of course there are nowhere near a thousand rows of data stored in here I've only backed up two databases since installing this instance of sequel server and those are the two apt and in this video so I've backed up the master database once and the model database once so this table keeps track of all the backups that we performed and it gives you all sorts of information about when that happened and so on and so on and so on um useful information for a system administrator of course for us not particularly interesting the other than to to reassure ourselves that that somebody's been keeping watch of the things we've been doing so far in this video um the query or the the code this member isn't out here to select the data from this this table we're gonna go into a lot more detail about select statements in later parts of this series of course so for now I'm just gonna close down that query window and then collapse up the system date system tables folder and the MS DB database just as with all the other system databases it's important to keep regular backups of ms DB so simply anything that can change the information in the MS DBH base should trigger a backup of that they threes itself so backing up and restoring other databases means you should really backup once again the MS DB database and once again is just as simple as backing up all of the other system databases I can like like MS DB choose tasks choose backup choose the location I want to risk a cup this this file to and then click the ok button very shortly which is enough with a backup of MS DB and once again just to quickly check that that's there I'm gonna scroll back to my backup folder and find that gun ms DB dr. back file there as well let's head back to sequel server management studio then we've got one more system database to have a quick look at so last bone Elise becomes the temp DB database and as the name suggests this database is used to hold all sorts of temporary objects both on G quite explicitly like temp tables and cursors and once created implicitly by sequel server itself sort of staging tables to hold temporary sets of data and the temp DB database is kind of unusual in that you can create objects in it but those objects get deleted automatically every single time when you restart the sequel server service so it really is the names yes and well as an S yes it really is a temporary storage space for sequel server so if I were to maybe create a new table just to demonstrate this if I right-click in the tables folder at empty B and choose new table and I'm just gonna create a really boring a game column name called test and I'm not even gonna bother changing the data type this time I'll close the table down choose yes to save the changes and I won't even bother changing the tables name and it's gonna click OK and then I'll find if i refresh the tables folder that that table will be sitting in the temp DB tables folder so if I now disconnect from that server that's not enough to get rid of the objects in the temp DB database if I reconnect to the server immediately and then I can have a quick look in the temp DB folder again so back to databases system databases temp DB tables there's my table 1 if I disconnect this time however and I restart my sequel server service so going back to sequel server configuration manager and then I can right click sequel 2016 training and choose to restart it and hopefully that won't take too long to do and when it has restarted I'm going to search straight back to sequel server management studio eventually and nearly there there we go so back to sequel server management studio really connect to the database engine and we'll see that this time if I go to the databases folder system databases temp DB look in the tables folder and that table has now disappeared so even though there's an explicitly named temporary tables folder everything in temp DB is temporary every single time you restart the sequel server service temp DB is recreated scratch one interesting thing about the recreation of temp DB is that of just like every other database you create it's based on the model database so if I were to add an object to the model database so again that should go for a basic table if I right click tables and choose new table and once can't give it a basic column name like test I won't bother changing the datatype I'll close the table down aren't you yes to save the changes and then I'll leave it with this default name of table 1 then when I disconnect from the server and once again hopefully fairly quickly just restart my sequel 2016 training service back in sequel server configuration manager and apology submit you watch this little Proteus bar one more time I guess you get used to this you'll be doing this quite a lot in the real world I guess if you're following through this series and there we go nearly there and when the service has restarted just switching back to management studio reconnecting to that database engine one more time and then having a look in the databases folder system databases temp DB because it's just been recreated based on the model template it's got all the objects from the model database installed inside it as well just to tidy up I'm going to go back to the model database and go to the tables folder and then right-click and delete my my temporary table there mom I guess my temporary table the one I was using to demonstrate two temporary tables and then I can collapse all that up and the next time I restart the service the temp TB database won't contain that table one last little thing to mention about the temp DB database is that unlike all the other system databases you come back up the temp DB if you right-click on it and choose tasks you'll find there's no backup option in the menu that kind of makes sense when you think about it there's no point in backing up temp DB if it's recreated from scratch every single time you restart the service as long as you've got a backup um with the model database and you've effectively got a copy a backup of the temp DB database as well okay well that's it for this basic video on the sequel server system databases hopefully at this point you've at least got a better understanding of what these four databases are actually useful even if you don't find yourself using them that much in the real world and if you do find yourself poking around and making a bit of a mess in any of these you also know how to backup and restore the databases to get yourself out of any sticky situations the next part of the series is going to focus on creating our own databases and in particular the next video is going to focus on creating tables and choosing the appropriate data types for columns so hope you'll join us for that one thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 47,117

Rating: 4.9265094 out of 5

Keywords: microsoft sql server, sql, system databases, master database, master, model database, model, msdb, tempdb, backup, restore, wise owl

Id: oOFrP3-qpSM

Length: 29min 37sec (1777 seconds)

Published: Mon Oct 31 2016