Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm playing around with SQL Server Agent alerts, hoping that I can get some reliable error notifications sent out when our transactional replication breaks. To test it however, I need to break it. I have a subscription that's safe to break, but I don't want to have to spend hours reinitializing and delivering snapshots afterward. Is there an easy, reversible way to break replication?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

ALTER DATABASE foo SET SINGLE_USER

Since the SQL agent jobs that perform replication cannot connect to a database in single-user mode, this has the simultaneous result of stopping replication, and disallowing client connections.

Ergo, replication is broken but it can be resumed without re-initializing (the LSN is still good)

share|improve this answer

What about testing it to perfection in a VM environment and then duplicating it to the real servers. A VM can easily be rolled back to a previous state so you can always start over again.

share|improve this answer
    
Effort. In our case it's fine to break replication in production for a minute or two, and far less of a cost than duplicating our environment. I sure wish we had replication in our test environment, though... –  Stuart Branham Jul 6 '12 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.