Yesterday I got a call from a client about MSDB being suspect after a drive failure. Unfortunately the client didn’t have any backups. Now the correct and best option is to use a backup and restore the database. This way you don’t lose any of the jobs and other configurations that you may have set over the years. In this case I didn’t really have much choice so after getting permissions to restart SQL Server we shut down the database instance and then copied over a fresh copy of the Data and log files for msdb from the below location.
“C:/Program Files/Microsoft SQL Server/MSSQL12.SQL_14/MSSQL/Binn/Templates/”
Once SQL server service starts it sees the new blank copy of the msdb database and the server is back to normal. Except for the missing jobs and backup history etc.
I would normally try my best to recover the data from the databases, but when it comes to system database there are limited options. It always a good idea to backup system databases yet I see that most companies tend to not give them the same level of importance.
The client had a user database too that was marked suspect and had allocation page errors which were not resolving even after DBCC CHECKDB with Repair allow data loss, in this case I didn’t really have much of a choice as this database was also not being backed up but after resetting the database status I was able to provide the client read access to the database so that important tables could be exported.
Please Consider Subscribing
