SQL Server MSDB Database Recovery

Here's how to recover your MSDB Database by restoring from a backup, using DBCC CheckDB, or by replacing the MSDB files in Windows Explorer.

Hello, my name is Curt. I’m a Senior Principal Engineer working with the GSE team. This video is dedicated to discussing the recovery of the MS SQL MSDB database. Methods for the MSDB database recovery and how we would carry out that recovery process. So there are three methods to recover the MSDB database. This is the database that's primarily responsible for containing job information, other tasks that are required for SQL Server, maintenance and upkeep and for other types of things at the program level, which may be extremely important to your database in your database production environment.

These three methods for recovering a damaged MSDB database are, number one, restoring from backup. The easiest method. We also have the opportunity to use DBCC CheckDB on this database as we would any other SQL Server database, and also replacing the MSDB database files is another method for being able to bring MSDB back online and back to a state where it's consistent. So let's go ahead and take a look at our different options here. Now with the GUI interface within SQL Server Management Studio it's a pretty simple process. Just go to ‘Tasks’ do a ‘Restore’ restore a ‘Database’.

If you have a backup with SQL Server that's an easy process to execute to get MSDB back online. Now let's take a look at the second method and it's using MS, I should say, using DBCC CheckDB. So we have three sets of commands here. The first set of commands is to find out where the consistency levels for the database currently exist for our MSDB database. Now we're not working with a broken MSDB database in this particular instance, so when we check for errors - allocation errors and consistency errors - we see zero in this particular case.

So yes it would be maybe more beneficial to see a damaged MSDB database with this recovery process, the demo process, but we're going through all the same steps that we would take If this were a damaged MSDB database. So in this particular case we just did a check, we didn't try to repair anything. If we do find a report of errors we would want to come back with the next command, with DBCC CheckDB. Once again keep in mind we're doing this because we do not have a good backup of the MSDB database to restore. If we have to try to do a repair based on the errors that we've seen here we can do the soft repair option, which is repair rebuild with DBCC CheckDB.

In this particular case we're going to go ahead and run it just to give you an idea what that looks like. Now with running this against a healthy database this is going to run rather quickly and with it not finding anything it's going to run extremely fast. Now if we do have errors, we do have inconsistency within the database it most likely is going to run longer, but the MSDB database is not a large database to begin with. After adding a lot of jobs it may be quite a bit larger but it's still not a huge database. If our soft repair option doesn't work and we continue to see errors we can resort to using the hard repair option which is ‘REPAIR_ALLOW_DATA_LOSS’.

We're basically telling SQL we want to do the best, we want to do everything we can to save this database. It's so important, it has important information within the database we're allowing the option to allow some database loss in order to get this database back online and available. So just running the same command it's going to look real similar to what we did with the soft repair. Now if we get to this point after running this, the hard repair, and we still see errors we may at this point have to resort to replacing the MSDB database files.

So in this particular situation what we're going to do is, we're going to go over to the installation directory for our instance of SQL Server. Now each time we install SQL Server it will install a ‘Binn’ directory, and within that ‘Binn’ directory we have a ‘Templates’ folder. within the ‘Templates’ folder we have the database files for all the system databases for this installation of SQL Server. In this case we need our MSDB database file and MSDB log files. I've already pulled a copy of these out, and we're going to come back over to where the active MSDB database files are located.

So what we want to do is pretty simple. All we want to do is replace these two files - MSDB data file and the MSDB log file. Now, in order to do this we will want to stop SQL Server. Having the opportunity to do this is extremely important because this gives us the opportunity to manage these files directly, and it allows us to bring in those replacement files. In this example, in this demo, we have our two MSDB files. It's not good practice to delete damaged DB files like this. We might be able to bring these up on another system, get into them and extract information so we're going to go ahead and move these files. I have a folder called ‘MSDB_save’. I've already copied these in, so let me backtrack here. In this case since I already have a copy of these I'm just going to go ahead and delete these.

Now our template files, let's go ahead and bring those in, after doing that we'll want to restart SQL. Now the downside of having to do this is any job information that was in there, anything at the program level that was developed after the installation of SQL and existed as a job is gone, because that went away with the old database files. Whenever we have to bring in the templates it's starting from Ground Zero as far as MSDB is concerned, and anything at the job level that was put in there after that installation will have to be put back in there after we bring in the template files. That concludes our demo on how to recover a MS SQL MSDB database. I hope this video helped you understand the methods to perform the recovery, and how to complete the recovery process.

Děkujeme za pozornost.