SQL Server DBA Tutorial 84-How to Rebuild MSDB in SQL Server

welcome to tech brothers in this video we're going to learn how to rebuild MSD B and this in this video is going to help you under these scenarios that your MSD B is in recovery state however you do have MS DB database backup that is available and database is good and restore well when MS DB is in recovery state you don't have any option to restore the database so basically you have to rebuild MS DB and then later on you can go ahead and restore your MSD B from the backup once it's come online so the things that we'll be learning in this video is number one how to start sequel server in master only mode because if you do that it's going to ignore model database and MS DB database and your services will start right away if you don't do that and you have a problem with your model model database then it's going to give you error that the model database it cannot find or a model database is corrupted so the services will not start in this particular demo we do need our sequel services to start even if we need to do it in master only mode so when we do that with this flag with this parameter backslash T that is three 60608 that means we're forcing sequel server basically to start in in master only mode it will only care if master data file and master log file is available so this is not the normal configuration of sequel server startup this is minimal not exactly minimal but it's a not normal configuration of sequel server startup and then after that we will be going through how to detach MS DB from master database because there is an entry in master database that has that will cause our script to go haywire so we need to basically systematically detach that MSD B from our master database backup and once we do that and then we're going to go ahead and run this i n st MSD b dot sequel script which is available in our install folder when we installed secret server I'll show you in a second that where the script resides all we need to do is go ahead and run that script and after we run the script it is very important to save the results in output and observe the results because there might be some of the things that it didn't it couldn't recover and you need to manually recreate those things basically after you create those are the system tables so those system tables are mandatory if it is missing any system table you can go ahead and take a look from the output file and then finally if you don't have the backup of MSD B all the all the user objects such as jobs maintenance plans all that all that kind of stuff needs to be recreated on newly MSD B but if you do have a backup that's well and good and backup needs to be recent if it's not recent then from that backup onward you will lose any changes that you have made in ms/ms dB so let's go ahead and basically do that we're going to go ahead and first reproduce that what the state we're talking about right here so let's go ahead this is my healthy Dynamics AX dev a sequel server instance as you can see that MSD B is online but we're going to make it unhealthy I have written down right the points here so we're gonna go ahead and basically turn the services off and rename MSD B MDF and LDF file so that it won't be able to basically find those files and that is just equivalent to corruption of MSD P so let's go ahead and go in configuration and stop the services sequel server services and we're gonna go ahead stop the agent as well stop the agents refresh and both are stopped so let's go ahead and go in the directory where our system files are so in my case if you go in sequel server right here my instance so I'm going to go in data right here is my M s DB data and M s DB log what I'm going to do is rename this underscore original so now if I go ahead and start the sequel server it's going to is going to start it but it's going to we'll take a look how it looks like alright let's go ahead and refresh as you can see the MSD B is in recovery state now if any database is in recovery state you cannot basically as you can see that it's not allowing us to basically go ahead and restore this database so we you have no option but to rebuild our database so let's go ahead and start the first step right here that start the sequel server will be using sequel command line and we'll go through all this right here so what we're going to do is first start the sequel services stop the sequel services and started with T 30 six zero eight so we're gonna go ahead and command-line let's go ahead and stop the services first stop I'm going to go ahead and stop all the services basically all right all the services are stopped so let's go ahead and run command line in administrator mode it is always good idea and sometimes is requirement to run command line in administrative mode so what I'm going to do is basically just copy/paste the script right here all right the services started in data mastered mode only that means it's caring about the master only so let's go ahead and refresh that let's disconnect and connect all right our MSD B is still in recovery mode so next we need to do is go ahead and detach MSD B from our master let's go ahead and detach MSD B from our master here's the command we're gonna go ahead and run this command okay so our next target is to run the script which I'll show you right here if you go let me go ahead and first show you the script right here the command that we need to run sequel CMD and then this is our sequel right here sequel server host and sequel server instance and right here is AI NS install MS DB sequel and I'll show you that where it reside and we're going to go ahead and put the output in right here we can go ahead and put install MS DB that would be our output file and we're going to observe that let me show you where this basically installed my MSD the script exists so we're gonna go ahead and go in our instance Program Files Microsoft sequel server and this is our instance so we're gonna go ahead and go in install and if you notice right here that this comes from Microsoft so this is not my script right here so install MS DB so what we're going to do is go ahead and copy this script to C and because in my script I just put it that on on C so we're going to go ahead and put it on C you can put it maybe we can put it right here and change our path in our command so let's go ahead do this so this is our script right here so we're gonna go ahead and run this script and once this script is completed we should have our MSD be new MSD be created we'll go through the output file which is installed MSD B and we're going to go ahead and basically put this file under sequel sis this backup directory as well so that we can go ahead and take a look if it has any errors when it creates new MSD be it creates also the system tables that's necessary for MSD B to work correctly so we'll be looking at the script that if it has missed any and if it has missed that we're not concerned about that's okay so we're gonna go ahead and copy this command let's make sure that our sequel server is hostname and sequel server is okay Dynamics AX dev okay we're gonna go ahead and run this script it's going to take about three to four minutes in my case it basically creates M s DB data and log file in the beginning in the script you can open the script basically and take a look and it takes time to create the system tables so it has already completed let's go ahead in our directory now - this is our install MS DB text file output file so let's first take a look if ms DB is created so we're going to go back to our sequel server instance right here and go to data and as you can see timing 6 8 16 and ms DB log and file are created let's go ahead and turn the services on all right all the services are on let's go ahead and connect with our sequel server we're going to run MS Microsoft sequel server management studio so going to go ahead and connect and see if our system databases are back as you can see ms DB is back and let's take a look on the tables all the system tables are ok looks ok so our final up here as we said that let's go ahead and observe the save the output in the result and observe the the results so let's go ahead and take a look on that we have our text file output text file right here so this is our output text file and let's look find any error that's ok that's our log that's error log and we got one error which is system collector it's okay we are not using system collector but when we use it it will create the stored procedure our system collector is not enabled on this particular database that's why it's throwing error and these are two errors system collector error were okay with that these system collector errors because we don't have system collector enabled so that's fine with us everything worked fine so now we can go ahead and restore our database ms DB database if you don't know how to basically do that I'll go ahead quickly use management studio and restore tasks go ahead restore database and in my case the backup resides basically takes always a little time all right we're going to add our backup device right here which is sequel server backups here is our MSD B and we're going to go an option replace it and close the existing connection click OK okay our MSD B is restored to our good state so basically this is it this is how we rebuild our MSD be and how we restored secret server in master only mode this is again the not normal configuration of sequel startup and we also did the detach how to detach ms TV from master database and created new MSD B and finally we restored our MSD be from our backup and I hope this video helps

Channel: TechBrothersIT

Views: 13,929

Rating: 4.8666668 out of 5

Keywords: How to Rebuild MSDB in SQL Server - SQL Server DBA Tutorial, Database Administrator (Job Title), Microsoft SQL Server (Software), SQL, Server, Developer, DBA, 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: Sx4AhJs6oe8

Length: 15min 8sec (908 seconds)

Published: Thu Mar 19 2015