Solved

SQL Replication

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Tired of waiting for your show or movie to load?  Are buffering issues a constant problem with your internet connection?  Check this article out to see if these simple adjustments are the solution for you.
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…

770 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