We have a SQL SERVER 2005 with 700 databases running.

Our idea is to replicate all the databases on the server with the SQL SERVER Replication Services (http://msdn.microsoft.com/en-gb/library/ms151314(SQL.90).aspx) to get two identical data servers running at the same time.

We've achieved to publicate a single database and subscribe this database in other SQL SERVER but our problem resides in that we can't publicate all the databases at the same time in a single publication (we can register any database in a single publication and subscribe this publication in the other server manually but we don't want to do every time which a database is created or eliminated).

How can i publicate and subscribe all the databases automatically between the two servers?

  • Are you looking to do this to provide a standby server or do you want both servers hot and accessible? – Chris W Aug 28 '09 at 10:14
  • Hi Chris, we want that both servers are accessible to use the second server like a mirror in case of error in the main server. Thanks – vfportero Aug 28 '09 at 10:35
  • How did you make out with this? We're in a similar situation, but with ~2,000 databases. – Beep beep Aug 15 '13 at 18:37

You can setup replication using code, this was done as a part of a project I was on years ago so I can't remember the detail. But basicly we setup the publisher and subscriber using code from C#, I think for the most part it was calling SP's in the server to do it.

So whatever code you are using to setup the database, you should then be able to add in code after that to setup the replication. But it will need to be done by creating your own stored procedures to basicly stitch together what you need to do.


There's no way to automatically setup replication when you create new databases. Or to have replication remove the replication when you drop the databases.

To have something automatic you'll need something that handles the replication at the disk level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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