May 6, 2012 at 2:16 pm
I have a request from the development manager to populate our new test environment with selected data from our production database. I'm looking for some alternative ideas as to how to make this happen.
Thanks in advance.
May 7, 2012 at 2:07 am
1. copy the backup to local/shred drive(accessible) to dev environment and do the restore.
2. use ssis package to migrate the data and save it too for later use.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 7, 2012 at 4:59 am
The simplist option is to restore the production database on your development environment. I agree with the previous poster, you should set up scripts to clean out any sensitive information.
After that your choices are all slightly difficult in one way or another. You can use SSIS, but you'll have to spend a lot of time getting it set up. You could use sqlcmd and bulk extracts, but it's going to be about the same amount of work to set up. You could try linked servers and queries, but that's going to perform slowly AND take a long time to set up.
You could look at third party products that can help. Red Gate SQL Compare[/url] and SQL Data Compare[/url] can get your structure and data out of production and move it to a seperate environment. The basic set up of these is very fast & easy. Automating them is a little more work, but not crazy. Disclosure, I work for these guys. While we're talking about them, they also offer a utility can mount database backups as if they were databases, SQL Virtual Restore. This can be a great way to set up a copy of production in your development environment without actually needing all the disc space required for production.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2012 at 6:15 pm
Thanks, actually I'm using redgate virtual restore because our current QA/DEV Environment does not have the disk space.
Really great tool. I really don't understand why the wouldn't
want a mirror image of the database out in DEV. I agree that
data(i.e. addresses, etc) should be sanitized. Unfortunately I
work in an environment that is pro developer. You would think
they would want to develop in an environment as close to prod
is possible. At least for stress testing.
Thanks
May 8, 2012 at 12:05 am
Classic dev/dba/qa concern - without consistant testable results QA and the DBA's are kind of stuck as there is no clear answer in regards to whether the data returned is actually correct as they can't define what is correct, devs would love/often have to work against the latest greatest version of the database - QA be damned.
I've seen a lot of variations on this theme over the years - the best option that I've seen actually work for everyone is to refresh dev/test from prod on a fixed and very inflexible schedule combined with a base set of tests that defines the current status quo (e.g. expected result for units tests) to everyone's satisfaction - asking QA/DBA's, etc. to shoot at an always moving target is a no-win for everybody including development - if x is a constantly changing number, determining whether you are right or wrong is nearly impossible.
Refresh from prod frequently but only on a well defined and understood schedule that allows for baselining expected results for key operations in an automated fashion.
Joe
May 8, 2012 at 3:52 am
My experience is the same, a regular refresh to ensure that data reflects production as much as possible. The issue is, this is only viable when the databases are of a reasonable size. As you start to move into the TB+ range, it becomes harder & harder to just slap copies of the database all over the place. The constraints of the physical universe start to step in then, even if you have a great piece of software like Virtual Restore.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2012 at 7:21 am
Yep. Well thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply