Advice about how to refresh a test database that is acting as a publisher, distributor, and subscriber (not replicating to other servers though)

  • 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 🙂

  • 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

  • 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:

  • SSIS is definitely the way to go. Backup/restore would require dropping/re-setting up replication.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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