August 5, 2009 at 8:11 pm
Hi,
I have a task to automate copying all objects (tables, views, SPs, functions, etc) except those belonging to a particular schema from database A to database B which are on the same server. The objects get added to the database A all the time and some may get deleted from time to time. I need to make sure that the setup picks those changes automatically. What would be the best way to configure this?
Thanks.
August 5, 2009 at 9:12 pm
My initial idea was to dynamically build a snapshot publication including only the articles required, build and re-initialise the subscription, drop the publication and then disable the publishing database for replication, as the database “A” gets refreshed from a backup every night.
But there may be better ways to do this.
August 6, 2009 at 2:47 pm
Hello,
A replication could be a solution, but, you do not mention nothing about the amount of the tables to be copied. If the amount of table is huge, replication is going to be a headache, if not, would be a good solution.
A scenario can be :
In the schema to be copied, you have a thousands of objects and the second schema just have a couple of objects, and almost all the database will be copy, you can perform a backup-restore and then delete the objects not wanted.
Can you give us more details about the amount of objects on the differents schemas?, in order to be a better idea of your scenario.
Regards,
Victor Alvarez
http://sqlpost.blogspot.com
August 6, 2009 at 5:21 pm
The size of the tables is not that huge, the snapshot takes only a minute to run, this may change in the future, I don't know. As for backup/restore, I forgot to mention that the database "B" can not be deleted, as it contains its own objects, which have to be preserved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply