May 2, 2013 at 7:56 am
We have Clients that will restore a Production Database into a Development/Testing Server. One of the options that we have configured is SQL Replication and because of that there are frequently issues because the Publishers are not dropped after the restore has been completed.
We have done our best to automate this process by providing instructions and script and the like, but like many things some of the instructions are not followed and problems occur.
To my question, does anyone know of a way to check if a database is configured for replication via T-SQL? I know how to drop replication for a database by using the sp_removedbreplication command, but I'd like to make it dynamic if possible so that checking can be completed before just blanket issuing the command.
Any and all comments and stuff is appreciated.
Regards, Irish
May 3, 2013 at 9:58 am
Use this.
if exists (select 1
from sys.databases
where is_published = 1 or is_subscribed = 1 or
is_merge_published = 1 or is_distributor = 1)
begin
--yourcode
end
May 10, 2013 at 6:32 am
Neeraj,
Thank you for the start, this has helped me a great deal.
So that anyone else can benefit, the code block that I am going to use looks like this:
SELECT databases.is_published, databases.is_subscribed, databases.is_merge_published
FROM sys.databases
WHERE databases.NAME = DB_NAME()
AND databases.is_published = 1
OR databases.is_merge_published = 1
What I want this to do is check to see if the database I am currently connected to has replication or not and then I might issue a command to drop the replication or print out a message. I will have to complete some additional testing.
I will post more when I have more code.
Regards, Irish
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply