SQL Server DBA Tutorial 83-How to Restore Master database in SQL Server

I'm detective others in this video we're going to learn how to restore master database the scenarios that this particular video is going to help you are you have a master database backup master database backup that you have is most recent and is in good state when I talk about most recent and in good state is that once you restore your master database backup you will not lose any data and that's probably the whole purpose of restoring your master database backup one of the purpose and in good state means that is restorable and master data files is corrupted let's say that that could be another scenario that your master data files is corrupted and other scenario could be that you have some change some system configuration and it messed up your master database and you don't really know what configuration that you changed in sequel server so you wanted to go ahead and restore your master database however do plan and outage because restoring a master database needs to have sequel server instance run in a single user mode that means no application can connect to sequel server but assist admin once it's admin account so what we're going to do in this particular what we're going to learn in this particular demo is how to run sequel server in a single user mode which I as I said that is a prereq of restoring master database in sequel server number two how to restore master database obviously this is this tutorial is all about that and number three resolution to the error that you can't log in because sequel server is in single user mode so let me put it right here real quick single user mode the reason I put this a third option even once you restart your sequel server in single user mode only once this admin account needs to can acquire a connection to sequel server let's say that you know a lot of organization believe me the environments are not all that perfect because we have some service accounts that's coming from different application and we have given them assist admin access and they're continuously pinging sequel server as soon as they cease server can accept a connection they get into sequel server so in the mean time when you put in single user mode and you take a little bit time to go to connect to sequel server and in the mean time that that particular one account comes in and acquired the connection that means you're out of luck it won't let you get in because only one admin account can get into sequel server instance so we will be seeing a logical trick to do that and that means that you need to just go ahead and stop the service start the services right away and try to acquire acquire the connection even though we'll make every effort to have all the applications stopped let's say that you did that and also you have made sure that all the services that is coming to the sequel server component services that's coming to the sequel server and have a sysadmin account you made sure that those services are stopped so they will not try to come into sequel server when you put sequel server in single user mode in spite of all that you might run into this issue but no worries about that we'll take care of that so let's go ahead and do this here's my sequel server this is my sequel server Dynamics AX dev is my host name and sequels sequel test is my instance name and here are my master database system databases right now I do not have any user database this demo is particularly about just restoring master database we can use two methods to do it one using sequel server management studio which is a bit hard in a way that you know every every time you click on new query it try to acquire a new connection which week it will show you that how to trick you know sequel server to let you do that and also sequels CMD we'll be using that so we'll be using first sequel cc CMD command line and then we will go ahead and use a sequel server management studio here is my let's go ahead and here is my secret server system back up my master bak is my backup right here so this is the file that we're gonna use so let's go ahead and first thing put sequel server this particular instance in single user mode so in order to do that you need to go in sequel server configuration so here is the sequel server 2014 configuration manager so we're gonna go ahead and click yes as you can see that the few components of sequel server is running on this particular instance one is sequel server browsing services other is sequel server agent services sequel server for these it's always a good idea to go ahead and stop these services so we're gonna go ahead and stop all these services but sequels are referred now all right all these services has stopped so let's go ahead and put sequel server in single user mode you know in order to do that you right-click go to the properties of sequel server right here in configuration go to the startup parameters and - M is the option to star sequel server in single user mode so let's go ahead and add that apply is gonna tell you that these changes will not take effect until you restart the sequel services and that's the reason when I said that plan outage because you are bringing sequel server services down so go ahead and click OK and we're going to go ahead and restart the sequel services services this will put sequel server in single user mode and we will verify that sequel server is in single user mode okay let's let's go ahead and refresh this as you can see that log-in failed user dynamics server is single user mode so let's go ahead and and apply that trick that I was telling you up here what we need to do since we can't really if we go the databases and click on new query as you can see the log-in failed okay let's go ahead and keep it just like this and restart the sequel services so and bring your ok sequel services restarted and let's go ahead and click on connect now as you can see it's connected so if you can't connect just keep restarting the sequel services that's the drink I wanted to share with you because what happened is as I said that there are application who are connecting with sequel server instance constantly as soon as they see that sequel server is accepting a connection they will acquire the connection so this is the way to to just really kill any existing connection and right away acquire the connection so what we're going to do is we're gonna go ahead and close this which will kill our connection and go ahead and open the sequel CMD so that killed our connection this is CMD in order to connect with sequel server using Co sequel CMD you need to run a command circle CMD - server and then you need to put Dynamics AX dev which is my host name where my sequel server instance is running and sequel server instances sequel TAS and if I click yes it's kidnet is connected now there's only one user connected so we're connected with that let's go ahead and basically run the command to restore the database sequel server database so we're going to go ahead and restore database master master and we're going to do drive equal and then we need to give in single quotes the path to our sequel server master database right here and backslash Master dot bak and this is our backup file and we're going to use with replace this is going to replace the existing master database so we're going to go ahead and do that like yes and let go in let's see what we do device right here we really meant disk not drive its Oracle coming in me so we're gonna go ahead and change this to disk and up here from disk from disk so let's go ahead and run this as you can see that master database is restored successfully so let's go ahead and do this same thing and we're going to go ahead and get out of this session so we got out of this session so let's go ahead and use sequel server management studio and try to do the same thing and as I said that you might run into an issue that it's a single user mode so you can't really connect to it so we're gonna go ahead and restart sequel services it's still in single user mode so we're gonna go ahead and connect alright so basically when I took the backup this particular Dynamics AX dev razza account was not there so what we're going to do now is go ahead and run with sa account alright let me go ahead and bring sequel server and multi-user right now and we'll create an account and then we'll go back to our experiments click OK restart sequel server in multi-user mode we're going to run as a different user Oh okay so we're connected what we're going to do now is create a login because we basically restored the master database so all the logins that we have created prior to the prior to the restore it's going to really go away so all the settings that we did all the changes that we did after the backup it's not going to be in our sequel servers so what we need to do is go ahead and disconnect this since we have created let's go back and put sequel server in single user mode why ok and restart let's go ahead and run sequel server management studio as an administrator and try to get into sequel server alright as you can see we are in sequel server if we try to go in master database and click on new query it's going to tell us that ok it's in single user mode which is fine let's go ahead and do the same trick that we did earlier all right services restarted so we are in now so let's go ahead and restore the databases restore database master from drive master tak with replace so when I go ahead and execute this I keep forgetting the disk and drive all right all right our master database is restored so this is how you restore your master database using sequel server management studio now you can go ahead basically and restart your sequel server in multi use multi-user mode so we're gonna go ahead and take remove this - M option apply click OK ok and let's go ahead and start the services and start all the services that we stopped prior to the restore ok so let's go ahead and try to acquire another connection yes we can try to do it one more and let's try now the CMD so what we're going to do is sequel CMD - s Dynamics AX dev that's my host name sequel test is my sequel server instance name and this oh we just restored the sequel server right here let's go ahead and basically create the log in again so what we're going to do is create this log in and try to connect again let's go ahead and do this so we're connected basically these are all intentional errors that what I were trying to do I mean I wanted you to learn every aspect of it and I wanted you to go through all the errors that you may find during your restore and how to resolve them so basically this is it this is how you restore your master database and we learned how to run sequence over in a single user mode we learned how to restore master database and a resolution to the can't log in sequel server is in single user and I hope this video helps

Channel: TechBrothersIT

Views: 40,411

Keywords: Restore, Master, Database, Database Administrator (Job Title), Microsoft SQL Server (Software), SQL, Server, Developer, DBA, Interview, DBA Tips, SQL Developer, SQL Server Interview Questions, TSQL Interview Questions and Answer, SQL Server 2014, SQL SErver Adavance Tutorial, SQL Server Tutorial for beginners, SQL Server 2014 DBA, SQL Server DBA tutorial for Beginners, SQL Data Adminstoration Tutorial Step by Step, TechBrothersIT

Id: DU8kaBuKQMI

Length: 16min 55sec (1015 seconds)

Published: Thu Mar 19 2015