February 4, 2013 at 9:25 am
I am hoping to get some guidance about setting up a weekly refresh of data from production to test. I want to use SSIS but I am open to whatever method makes sense with the following scenario:
prod server one (database named A) is the publisher and distributor to a database on prod server 2 (A_REPL).
prod server 2 (database B) is the publisher and distributor to another database on prod 1 (B_REPL) (so 4 databases so far on two servers).
the test server has two databases that replicate to two other databases all on this test server (4 databases and 1 server) (named A and A_REPL and B and B_REPL)
Now I want to refresh the data from prod 1 one of the databases (database A)) to the test server (one of the publisher databases (database named A on this test server)).
How can I refresh the data without messing up replication? Is SSIS the way to go? I would do this after hours.
Thanks in anticipation of any advice anyone may have. General suggestions or specific details appreciated. Whatever you have time for 🙂
February 4, 2013 at 10:00 am
As with almost everything we do there are multiple ways of doing a task. A method I use to refresh test databases from production is with a Windows Scheduled task to call a PowerShell script that copies the database to test and then run an Job that restores the database. Not replication but it should work for you as well.
______________________________
AJ Mendo | @SQLAJ
February 4, 2013 at 10:16 am
Thank you SQLAJ. Yes there are many ways, for example, I can use SSIS and everything works beautifully but that's without replication. I was hoping that someone could give me an example of a successful refresh with replication already in place for other reasons unrelated to refreshing. It doesn't mean that the refresh solution should involve replication, just that replication needs to be accommodated. I have been searching for an answer to this for quite a while and have googled my fingers to the bone without finding any examples so I thought I would post. I usually don't post until I'm despairing of ever finding a solution to somethimng. :unsure:
February 4, 2013 at 10:25 am
SSIS is definitely the way to go. Backup/restore would require dropping/re-setting up replication.
February 4, 2013 at 10:46 am
Thanks Roger. I was hoping to use SSIS. Have you had to refresh a database (mine's in test) that was acting as a publisher and distributor to another database? If someone tells me that this can be done and possibly some things to watch out for to not break replication, I would be very grateful. My only past experience (on another job) with replication was hourly snapshot and it wasn 't complicated. I am studying replication now but I'm hoping to try to get this issue solved sooner than I'll be able to turn myself into an advanced replication student though.
I will definitely mark my post as answered when I have at least a little more info. Thanks everyone for reading my post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply