SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Advice about how to refresh a test database that is acting as a publisher, distributor, and...


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

Author
Message
pharmkittie
pharmkittie
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 867
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 :-)
SQLAJ
SQLAJ
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 410
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
pharmkittie
pharmkittie
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 867
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
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2734 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
pharmkittie
pharmkittie
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 867
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search