Solution to keep dev environment data in sync with live

[divinyl]

hi there

i'm a dba and i am constantly being asked by the dev team for assistance with keeping their dev environments in sync with live in terms of data. Possibilities are DTS'ing data out on a nightly basis to their environments but that wouldn't work well for our replicated environments where there are updates happening at the subscribers, and even if there was no replication, our databases are hosted in shared environments and i wouldn't want to have these uge massive DTS jobs running every night and adversely affecting our live servers.

Does anyone know of a good solution for doing this??? Would appreciate any tips!!

Thanks
Di

 

[mutley1]

Don't know how big your DB is, but you must be backing up live. why not ship a copy of the .bak file to the dev server? Sorry if I'm missing smething in the architechture, but this seems a bit simpler as opposed to DTS ing everything over.

Cheers,

M.

 

[divinyl]

Hi - thanks for the response.
Yep, backups would be an easier method but there's an issue in that there are certain columns that will need to be scarmbled before its shipped over to dev (passwords, e-mail addresses, etc). I'm thinking the only way to get around that would be to DTS the data to a staging db, run a script to scramble the sensitive info and then DTS the staging data to Dev...

We have hundreds of databases, some are pretty massive, and whatever solution we provide has to be available to all development teams. The thought of hundreds of backups copying over the network every night, or even once a week, is a little scary...

Any other ideas?????

thx
Di

 

[mrdenny]

I had to do this once at a company. I setup an SSIS package and set it up to copy over a small sampling of the database. I would usually bring over a random set of 1-5% of the database from production to development.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog