Methods to data refresh from production database to QA & Dev databases

Question

Post reply

Methods to data refresh from production database to QA & Dev databases

gary1

SSC-Insane

Points: 21152
More actions
September 10, 2010 at 9:42 am

#226801

Hi,
We are using SQ Server 2008 and we need to refresh the data from Production database to QA & dev weekly basis and to another Reporting database on every night.
Could you please advice me the available methods to achieve this in SQL Server 2008..
Production database size is 250 GB.
Thanks

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

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

jboudinot Mr or Mrs. 500 Points: 558 More actions · Answer 1

jboudinot

Mr or Mrs. 500

Points: 558

September 10, 2010 at 10:01 am

#1219315

One way would be to set up a backup/restore process.

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

Weekly data refreshes? If you do backup/restore, that also means anything being developed/tested will be wiped out....typically I only do data refreshes after a major release when there's a code freeze.

For a reporting database, you could keep it relatively up to date with either log shipping, replication, or mirroring and creating snapshots.

gary1 SSC-Insane Points: 21152 More actions · Answer 3

I want to use Backup/Restore method for the first time and then I want to refresh the data only from Production database. So in this case which method is the best option to go for?

Like we use Data pump in Oracle. Just one time setup and run when ever you need to refresh the data

Thanks

caladba SSCommitted Points: 1514 More actions · Answer 4

As Derrick mentioned...

To update the dev db can be a bit tricky and really depends on the dev team's requirements.

Does the dev team want to keep their inserts and updates?

If so, is it important to for the records inserted by the dev team to keep their unique idenfiers?

Are these identifiers GUID or IDENTITY?

If they don't need to keep their changes, backup and restore is the best and the simplest way to go.

To update the report server...

My personal recommendation would be SSIS, especially since the report server only needs to be updated once per day.

SSIS moves data quick and you can manage them with source control without having buy a third party tool.

It also scales better than replication as your db size and activity grows.

It's main intended use is for data dumps/inserts so if you need to merge/sync, make sure you do your research on the best implementation using SSIS.

Chris Johnson-445534 SSC Veteran Points: 250 More actions · Answer 5

I've used a tool called SQL Data Compare to sync data between test and production databases. It has an API available that allows the process to be automated.

Chris

Thanks,

Chris

Newtek Web Hosting

caladba SSCommitted Points: 1514 More actions · Answer 6

Lucky!

We've been trying to get it, but obviously had to do without it so far...

I've never used it, but it sure would have save me a lot of time 🙂

gary1 SSC-Insane Points: 21152 More actions · Answer 7

How about Import/Export?

Can we use Import/Export to do data refresh from Production to Dev/QA?

In what scenarios, we use Import/Export?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

gmamata7 (9/10/2010)
I want to use Backup/Restore method for the first time and then I want to refresh the data only from Production database. So in this case which method is the best option to go for?
Like we use Data pump in Oracle. Just one time setup and run when ever you need to refresh the data

Three questions...

1- How often you have to run the refresh process?

2- How big is the affected database?

3- Is it a full refresh or are you refreshing a subset of objects?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

gary1 SSC-Insane Points: 21152 More actions · Answer 9

To be specific, I'm just in analyzing the best practices to refresh the data using SQL Server 2008 .

Because now we have the databases in ORACLE and we refresh the data daily for ReportServer using data pump.

And whenever the developer requests, we refresh the data according to the request. Sometimes only data and some times specific objects only

Now we are testing the Migration of data from Oracle to SQL Server 2008. So I just want to get aware of the data refresh methods and test them in this testing phase.

Thanks