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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Advice about how to refresh a test database...

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

Rate Topic

Display Mode

Topic Options

Author

Message

pharmkittie

Posted Monday, February 04, 2013 9:25 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:53 AM
Points: 23, Visits: 228

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 Smile

Post #1415372

SQLAJ

Posted Monday, February 04, 2013 10:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:21 AM
Points: 85, Visits: 361

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

pharmkittie

Posted Monday, February 04, 2013 10:16 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:53 AM
Points: 23, Visits: 228

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

Post #1415396

Robert Davis

Posted Monday, February 04, 2013 10:25 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 1,560, Visits: 1,408

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

My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #1415397

pharmkittie

Posted Monday, February 04, 2013 10:46 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 06, 2013 9:53 AM
Points: 23, Visits: 228

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 »

Permissions