I’m sure most of you have detached a database, maybe to move it to
another server, or just to move it to another drive. Did you know that if
you’ve enabled replication for the database you can no longer detach it?
I ran into this a couple years ago. We had added disk space and I
needed to move things around, but almost every database I have is replicated.
It seemed like there should be an easier way, but sometimes you go with what
you know (and is recommended), so I scripted out all the existing publications,
removed replication, moved the database, then ran the scripts to add
replication back. It takes a good amount of time and effort if you have many
publications.
All of the following comments apply only to transactional replication,
I would expect similar behavior with merge replication but I have not tested
it.
Just in thinking about it, I saw no reason detach shouldn’t work. If
you detached the database and log file, the log file would contain any
committed transaction that had not yet been picked up by the log reader.
Provided you reattached the same files back to the same server it seemed like
everything would pick up right where it left off.
To start testing I set up a copy of Northwind, enabled transactional
replication, and created a publication containing only the category table, and
then created a subscription to a new database called NorthwindCopy2. After
verifying that replication was working, I went back to make sure I was sure
about the detach behavior. I tried three separate methods and all returned the
same result – failure. Those methods were:
- Detaching via Enterprise Manager
- Running sp_detachdb from Query Analyzer
- Running DBCC Detachdb which is what really does the
work
Clearly it was able to discern that the database was published. I
remembered seeing an sp_dboption switch, so I started there. Syntax is
straightforward:
Sp_dboption ‘NorthwindCopy’, ‘published’, false
Running it successfully changed the publication status, it also wiped out the publication! Obviously someone is serious about not letting mortals detach a replicated database.
So, time for some deeper study. Assuming the info about replication is
probably tied to the database, I query sysdatabases. Nothing obvious, so I jump
to BOL. Sure enough, the category column contains a flag for replication and my
test environment confirms the settings show below.
Because I needed to modify a system table, I needed a couple extra
steps before I could try the detach again:
sp_configure 'allow updates', 1
reconfigure with override
update sysdatabases set category=0 where name='northwindcopy'
At this point if you refresh Enterprise Manager the database no longer appears to be published and the publication folder is gone. Detaching the database works without an error as expected.
The first difference I noticed from the early sp_dboption attempt is
that the replication jobs were still present. A good cleanup would have removed
them. The log reader job had failed indicating it could not run sp_replcmds,
which makes sense since the database has been detached!
Before I tried to put things back I wanted to rule out one area of
concern I had – what if the database id changed? In my test case the removed
database had been 9. I ran a create database statement to create a placeholder
database and verified it had been assigned 9, assuring that when I reattached
my original database it would get a new id.
I reattached using Enterprise Manager, and then ran the opposite script
from earlier:
update sysdatabases set category=1 where name='northwindcopy'
Refreshing Enterprise Manager showed the database as published, the
publication was present again, and when I restarted the logreader job it ran
without error. I was able to successfully apply transactions to my subscriber
database.
Caveats? A few!
- This is only going to work if you reattach to the same server AND you don’t change anything else related to replication (distribution database, jobs, etc)
- You’re making a change to a system table. Not a reason to shiver in fear, but exercise caution, and be sure to reset allow updates back to false when you’re done
- I have not tested this with merge replication
- It’s probably not going to be supported if something goes wrong – have a backup and a script of your publications
It’s not a technique you’ll use often, but maybe one day it will save you some time.
