Solved

SQL Replication

Posted on 2014-03-19
1
135 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

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
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…

910 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

18 Experts available now in Live!

Get 1:1 Help Now