Options to handle corrupt msdb ldf file

database-administrationreplicationsql-server-2005transactional-replication

Specs: We're using SQL Server 2005 with transactional replication.

Our MSDB .LDF file has recently become corrupted, causing the MSDB db to be marked as 'suspect'. We read a bunch of stuff online and learned that to fix this you either have to restore from a backup, or you have to rebuild MSDB from the sql install script.

Since we don't have a backup, we tried running the install script, instmsdb.sql (after dropping the msdb db by running the sql server service using the -T3806 -m -c startup params and all that jazz).

This was successful, so I'd planned on rebuilding the distribution db and publishers, and then reinitizaling the subscribers. However, when I tried to access anything in the Sql Server Mgmt Studio menus regarding replication, I'd get an error about missing tables in msdb. One of the specific errors is "invalid object msdb.dbo.MSdistributordbs".

So I figured the system is in an in-between state – the distribution system table is created, but the required replication-specific tables and sps aren't present in the msdb db.

So, my question is one of:

  1. What scripts or stored procedures need to be run so that msdb is replication ready, and/or,
  2. How do I manually remove the publishers and other replication related objects so that I can re-create all the replication stuffs?
  3. Is it even possible to load the msdb without the ldf file somewhere that i've missed?

Edit: One more thing I tried – I enabled replication on the subscriber server, copied it's msdb mdf and log files to the 'bad' server, and tried to attach those. However, I forgot the databases would be incompatible since the publisher is sql server 2005 and the subsriber is sql server 2008. Sigh. This might have worked otherwiser (well at least gotten to the point where I could remove and recreate the replication).

(And just as a side note, our 'sysadmin' in charge of db backup scripts didnt backup any of the system dbs. This has been rectified. sigh).

Best Answer

It's been a long time since I've had to use replication (and perhaps you've tried this) but are you not able to use the wizard to disable the publisher and distribution db and then re-enable them?

Related Topic