I often need to grab a copy of a production database so I can work with it on my local SQL Server instance (for testing purposes etc). Traditionally I’ve been doing this by taking the most recent full backup and restoring that. That’s time-consuming, though, and doesn’t necessarily mean you’ll have the latest data unless you also restore the logs.

So late last week I tried a different approach. Our database is merge-replicated across a bunch of servers, one of which is a reporting server that isn’t used very often. I visited that server and took the database offline while I copied the .mdf file down to my local machine. Once that was done, I brought the database back online and attached the copied .mdf file to my local server. It was a much quicker way to copy the database and I was online almost straight away.

So for this particular job I needed to add a column to a table. I went ahead and ran my ALTER TABLE statement against the newly-attached database, and was met with this error:

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 361
The data definition language (DDL) command cannot be executed at the Subscriber. DDL commands can only be executed at the Publisher. In a republishing hierarchy, DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.

Well, damn. The copy of the database has brought with it the replication information, which is telling my local server that this database is “subscribed” to a merge replication and can’t have its structure changed. The interesting thing is that this query:

select * from master.sys.databases

… brought back a 0 for all the replication-related columns (“is_published”, “is_subscribed”, “is_merge_published”, “is_distributor” etc).

In the end, I found a stored procedure that I’d never heard of before: sp_removedbreplication. I executed it like this:

sp_removedbreplication @dbname = 'pigfmcor'

And voilà! My database now allows DDL statements to be issued against it! Good to know, and handy enough that I thought it was worth blogging about.