Advice sought....populating a DEV copy of the database

Question

Advice sought....populating a DEV copy of the database

RedBirdOBX

SSCommitted

Points: 1836
More actions
May 23, 2014 at 12:58 pm

#291666

Hello all -
I'm seeking advice from the gurus in here. Like most development environments, I have a (large) production database and a "dev" database.
The DEV copy of the database is a mirror copy of the schema and structure of the PROD and it contains all the same objects (views, SPs, etc). But as time goes on, the data gets more and more outdated and out of sync.
I would like to, at any given time, repopulate the DEV database with fresh data from PROD. I cannot simply drop the DEV database and recreate it from a copy of PROD because the DEV might have works in progress such as experimental Views and Stored Procs.
So what I've been doing in the past is written a Stored Proc to truncate a bunch of tables and then reinsert data from the PROD database back into DEV. This has always caused me a lot of grief and headaches though. SQL Server is very fussy about relationships and constraints so I'm constantly having to work around the defined relationships and constraints..usually by trail and error. Such as, not truncating some tables but deleting and then reseeding them. Truncating in a certain order and then resinserting in a reverse order. Removing constraints in the Stored Proc and then re-adding them at the end. And so on. Hopefully, some of you can relate.
So...my question is this. Considering this is a large database, full of relationships and constraints, is there a better way to keep the data semi-fresh at regular intervals, without dropping and restoring a copy of PROD?
Should I look into Replication? What do the seasoned DBAs do around here?
Thanks!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

lptech Hall of Fame Points: 3208 More actions · Answer 1

You can script out the foreign keys, drop them, and re-add after copying over the data. The script library here has several such scripts. Use the import data wizard to create an SSIS package to copy over the tables. Finally, create a job to drop the foreign keys using the script output, run the package, and then re-add the foreign keys.

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 2

Could you elaborate on scripting out the FKs and then readding them?

Lowell SSC Guru Points: 323518 More actions · Answer 3

here's what i do:

i script out the DDL differences between the developers' version, and production;

then i restore a copy of production on dev, and run the script against the database. so that's zero data migration,and pure DDL migration.

that also gives me a preview/test run as to wehter potential changes might break wehen going against production as an added bonus.

If the developers have DML that needs to go with it ... ie, populate the new table(s) they've created, they had to have given me a script to run in addition to the DDL I generate.

then i simply rename their old database so they have a copy, and then rename my new one to match what they typically work in.

script wise, the restore process is fully automated for me...the DDL scripting i run on demand with either a VS database project, or a utility i wrote myself that does it about 10x faster than SMO can do it, as far as scripting db differences.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 4

That sounds like a plan! Could you please explain how you "i script out the DDL differences between the developers' version, and production"?

Thanks a million!

Lowell SSC Guru Points: 323518 More actions · Answer 5

RedBirdOBX (5/23/2014)
That sounds like a plan! Could you please explain how you "i script out the DDL differences between the developers' version, and production"?
Thanks a million!

There's lots of tools that can help you script differences; Redgate SQL Compare (Paid$)[/url] or http://opendbiff.codeplex.com/ or Visual Studio Ultimate(not VS professional)

all have the ability to do a comparison and produce a result script to apply against the "target" database.

so with a half dozen or so clicks, and waiting for the results to get generated, you have a script to apply to a database. easy peasy.

there's other tools out there as well, and like I mentioned, I created one myself as a proof of concept.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lptech Hall of Fame Points: 3208 More actions · Answer 6

Just type 'script foreign keys' in the search box on this page, and you'll see a number of script in the search results.

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 7

Note: with Visual Studio 2013, you do get the SQL tools with the Professional version.

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 8

Thanks everyone. After some trail an error, I was able to create a SP that does most of what I want it to. It's a whopper as it touch 40+ tables.

Essentially here's what I did:

*Count all records from all tables in the DEV db prior to truncating. Put in temp table

* DROP all the constraints in the DEV db.

*TRUNCATED tables in DEV and reinserted fresh data from PROD

*Counted record per table that were being inserted. Put in temp table

*ReAdded the constraints via script

*Displayed each table name and how many records were deleted and then inserted.

It works...for now. Thanks for the input!

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 9

RedBirdOBX (5/28/2014)
Thanks everyone. After some trail an error, I was able to create a SP that does most of what I want it to. It's a whopper as it touch 40+ tables.
Essentially here's what I did:
*Count all records from all tables in the DEV db prior to truncating. Put in temp table
* DROP all the constraints in the DEV db.
*TRUNCATED tables in DEV and reinserted fresh data from PROD
*Counted record per table that were being inserted. Put in temp table
*ReAdded the constraints via script
*Displayed each table name and how many records were deleted and then inserted.
It works...for now. Thanks for the input!

You could also make life easier for yourself in the future if you can separate data and code into different databases on the same SQL Server instance. We keep all of our code (stored procs, functions, views, etc.) in one database and all the data in another so that we can restore a backup of the production "data" database to the dev environment without affecting work in progress in the dev "code" database. We're also in the process of putting our "code" database in source control, and since there's no data in the "code" database, it's easy for developers to deploy multiple versions of the "code" database (with distinct DB names) to the dev instance for testing against the data in the "data" database.

Jason Wolfkill