?
Solved

SQL Replication

Posted on 2014-03-19
1
Medium Priority
?
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…

777 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