Recovering an MSDB database

Recovering an MSDB database in SQL Server marked as ‘suspect’.

Recently, after the server reboot, we got a message that MSDB was in a suspect state. Panic ensued. We had to repair it.! We thought it would be fairly straightforward to fix., but it was actually more complex than we thought.

Attempt 1.: Restore from a backup.

Well, our first thought was to simply restore the database from last night’s backup. Right? Wrong! We logged a ticket with the server team, asking for the backup to be made available. However, it would seem that there was no backup for this server. (it’s only a dev server). So we needed another solution. Even though it was only a dev server, it contained many months of work, with new tables, stored procedures, and triggers existing only within it and nowhere else.

Attempt 2: Copy another SQL Server MSDB backup.

Our next thought was to restore a backup from a similar instance in SQL Server with the same version and build number. This solution will of course result in data loss. You also have to restore from T-SQL, rather than from SQL Server Management Studio GUI!

Unfortunately, we couldn’t find a backup that matched the dev server’s version and build number, so this solution was not possible either. We needed to try something else!

Attempt 3. Use a SQL Server database Template.

Fortunately, there is another solution. When you install SQL Server, the install process creates a template for each of the system databases. For our version of SQL Server (2022), the templates are stored in C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Templates

We were able to shut down the instance of SQL Server, copy the template, and restart. The SQL Server instance started, without any warnings or errors.
At this point, we still had to recreate things like jobs and mail configuration, as we didn’t have any backups of those as SQL scripts.
In conclusion, I guess that the point is of all this is that you need backups, and a proper disaster recovery procedure even just on a dev server. Because you never know when you might just save your skin.