SQL Replication

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.

Who is Participating?
lcohanConnect With a Mentor Database AnalystCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.