Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Advice about how to refresh a test database that is acting as a publisher, distributor, and subscriber (not replicating to other servers though) Expand / Collapse
Author
Message
Posted Monday, February 4, 2013 9:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:52 AM
Points: 72, Visits: 448
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
Post #1415372
Posted Monday, February 4, 2013 10:00 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:57 AM
Points: 85, Visits: 401
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
Post #1415389
Posted Monday, February 4, 2013 10:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:52 AM
Points: 72, Visits: 448
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.
Post #1415396
Posted Monday, February 4, 2013 10:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:20 AM
Points: 1,616, Visits: 1,544
SSIS is definitely the way to go. Backup/restore would require dropping/re-setting up replication.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1415397
Posted Monday, February 4, 2013 10:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:52 AM
Points: 72, Visits: 448
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.
Post #1415406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse