Learn the solutions for recovering a suspect MSDB database in SQL Server
SQL Server agent makes use of msdb database for the purpose of scheduling jobs and alerts by making use of other features like SSMS, database mail, and service broker. There can be situations where your msdb database is rendered suspect and is in need of recovery. Here you might think that a simple recovery action would be enough to bring the database back in health, but that is not true. Here you need to do ensure more than just the recovery of the database. To learn what are the different ways out of this situation, continue reading.
1. Recover MSDB database using Backup
This might be your go-to solution because of the assurance of least data loss, but it is important for you to know that the amount of data loss also depends on how active is the database that you are using for recovery. If the transaction log is backed up frequently and the database is also active, you might just be able to recover 100% data. So make sure that you keep on taking regular backups so that you have at least something to fall back on. In case this does not work, you may consider using a specialized corrupted SQL recovery tool.
2. Get hold of an MSDB backup from another SQL Server Instance
This can be your Plan B. If you end up having a suspect msdb database and no backup to fall on, what will you do then? Make use of a different SQL Server instance and its msdb backup. However, make sure that the instance is similar to the version that you had been using, which has now gone corrupt. However, the key thing to keep in mind over here is that if the msdb database is out of commission, then the restoring will have to be done through T- SQL. As SSMS is unable to restore databases from GUI without the msdb already running.
And while you are making use of T-SQL for the same, make sure the built and the version are matching, otherwise, the transplant will end up in a failure.
3. Make use of MSDB Database Template in SQL Server
If there is no backup to recover from and no other SQL Server instance to make use of, then you need to make use of the templates that are created at the time of installation, for all databases. This option will be available to you if you are using a SQL Server 2008 or later edition. And on the basis of the edition in use, the storage path of these templates will differ.
Once you have located the template for the msdb database, you need to close the instance and then replace the suspect msdb, along with transaction log with their template files, and then restart the instance. This will launch your instance without any error!
Another solution that you might want to make use of is the msdb scripts, which allow you to recreate objects. But this can be done only if you have the scripts available with you. This is not really a recovery option, but something that can help you save time, by avoiding resetting all objects from scratch.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover access and sql recovery software products. For more information visit https://www.datanumen.com/