Solved

SQL Replication

Posted on 2014-03-19
1
132 Views
Last Modified: 2014-04-23
What is the easiest way to replicate a "live" SQL Server 2008 database to a development DB? Ideally, I would like it to replicate once a day, so I am working with fairly new data on the development server, then it is replaced the next day with a fresh copy.

Thanks
0
Comment
Question by:bezellvi
1 Comment
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39940210
If the PROD database is not big I suggest you just back it up (FULL backup) then restore it in the Development with overwrite but...with this scenario what about all development changes done in between? if Developers don't save their SQL code aside of their own data they will be overwritten.

You could use log shipping but the replicated database is not accessible like a real development database.

My suggestion is to try think at a differential backup approach like the following:

If the business rules are ok with it you setup your PROD DB FULL backup Weekly (on Sunday?) and Differential backup Every day.

Lets say you Script and save ALL the SQL code (views,functions,triggers,stored procs, etc..) from the Development database on Monday Morning.

You then restore the full backup of PROD database taken over the weekend over the Development database then put back ALL the Dev code you just scripted.

Carry on with both DEV/PROD

MAKE SURE that EVERY DAY you script ALL SQL Code objects from DEV

Restore the Differential PROD backup taken on Mon,Tue,Wed,....over the Development database and apply the DEV scripts back to the development database.


Obviously ALL the above requires a bit more detailing, testing and approvals as they involve your PROD backups as well.


Another option would be to create some SSIS package to Truncat/Populate your Development database from Production but again....this process may be broken by the Development cycle - adding/changing/dropping columns from the Dev database due to Dev cycle.
0

Featured Post

New My Cloud Pro Series - organize everything!

With space to keep virtually everything, the My Cloud Pro Series offers your team the network storage to edit, save and share production files from anywhere with an internet connection. Compatible with both Mac and PC, you're able to protect your content regardless of OS.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using in-flight Wi-Fi when you travel? Business travelers beware! In-flight Wi-Fi networks could rip the door right off your digital privacy portal. That’s no joke either, as it might also provide a convenient entrance for bad threat actors.
This Micro Tutorial will show you how to maximize your wireless card to its maximum capability. This will be demonstrated using Intel(R) Centrino(R) Wireless-N 2230 wireless card on Windows 8 operating system.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now