Recovering from a failed ldf + merge replication - 12-05-2005 , 04:51 PM
after reading a number of postings/articles about recovering from a
failed .ldf, i've decided i should go ahead and post my own message.
fortunately, i'm still in a test lab, so no harm done (yet).
here's is what i do to reproduce the situation.
1) create a database with tables in it
2) publish it via merge replication
3) net stop mssqlserver
4) rename the log file
5) net start mssqlserver
this results in a suspect database. fine, no problems. i've read
about the sp_attach_single_file_db, so i'll try that. first, i have to
detach the database...woops - it's involved in replication, can't do
that. so now, i have to disable replication. but, the whole point of
this exercise is to recover a published database, so i can't use
enterprise manager to do it, as i'll lose my publication and all
replication system tables.
instead, i temporarily allowed ad hoc updates, modified sydatabases to
force sql server to think it's not replicating, and then i can detach
it. great, that at least works. now, i have to reattach it with
sp_attach_single_file_db. unfortunately, this procedure strips
replication (as evident by it running sp_removedbreplication as it's
so instead, i just manually generate the CREATE DATABASE statement that
sp_attach_single_file_db would have done, and run it. this re-attaches
the databases with all system tables intact. naturally, since i
modified the database prior to detaching, it's now marked as a
non-replicating database. so, once again, i temporarily allow ad hoc
updates, and modify sysdatabases accordiongly.
the end result is a functional publication as originally intended
(confirmed with replication by multiple subscribers, one of them
vertically partitioned). however, i have this terrible sinking feeling
that i'm doing something completely wrong, and very ill-advised...
keep in mind before you respond - yes, plenty of backup schemes are in
place. but in my industry/company, ANY lost data is unacceptable.
recovering from the previous night's backup and/or log backups is not
good enough, if it doesn't bring me to the exact point in time (which
is why i can handle failed .mdf's - that's no problem at all).