'Refresh' data only from Prod to Test environments

Question

'Refresh' data only from Prod to Test environments

Viewing 5 posts - 16 through 19 (of 19 total)

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

tony28 SSCertifiable Points: 7112 More actions · Answer 1

Did you try something with 2option what I wrote? I think that with this you will spend little time and then you can use everytime when you want ...

tony28 SSCertifiable Points: 7112 More actions · Answer 2

1)Right-mouse click on database, click Tasks, click Generate scripts.

In the Script Wizard, choose script to file and select one file per object.

2) backup and restore on test

3) use this ,, pls first test on another test database, if is this what do you want

and dont use for system databases

Use DATABASE_NAME

DECLARE @name VARCHAR(max)

DECLARE @sql VARCHAR(max)

DECLARE CURSOR_T CURSOR

FOR

SELECT NAME

FROM sys.procedures

WHERE type_desc = 'SQL_STORED_PROCEDURE'

OPEN CURSOR_T

FETCH NEXT FROM CURSOR_T INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql = 'DROP PROCEDURE '+@name

exec (@sql)

FETCH NEXTFROM CURSOR_TINTO @name

END

CLOSE CURSOR_T;

DEALLOCATE CURSOR_T;

4) execute script from point 1)

Hope that will help

n.heyen SSCrazy Points: 2849 More actions · Answer 3

tony28 (3/3/2016)
Did you try something with 2option what I wrote? I think that with this you will spend little time and then you can use everytime when you want ...

Thanks for all of the suggestions (and code) Tony,

As of today, I've tried a couple examples using the Import/Export task and it seems to work well enough. I'm currently waiting for more clarification from the development team about what they really want. And how often. For now, I think I can manage to do this manually. None of our databases are really huge so they shouldn't take long. Sort of a cop-out I know, but until I get more information about just what they want, I'm not going to work too hard trying to guess.

Thanks!

Norman

aaron.reese SSChampion Points: 13498 More actions · Answer 4

I don't know if I have misread the OPs requirements or if you guys have all missed the point 😀

I think the development team want the data from production, but the objects from TEST so that the testing can be done on real data rather than the limited and shoddy data generated during the development cycle (not stated, but assumed). There are a number of reasons for this.

1) Testing with volume

2) TEST data may be in a corrupt state as referential errors were introduced during testing

3) You want to generate user documentation and you would prefer not to have Donald Duck appear in your customer list :hehe:

4) You want the users to do the testing / training and they feel more comfortable with the data that they know.

Redgate have a number of tools in this area.

SLQ compare can be used to compare the objects in the database and generate migration scripts for tables, indexes, triggers, views, synonyms, procedures, function, roles and users

SQL DATA Compare can do the same but for the data within the tables and this sounds like the tool you probably need. this is especially good for moving static data and configurations between environments - no point in hand coding the list of payment methods for each environment.

The toolbelt also comes with handy plugins that allows you to do source code control to Subversion, Mercurial, TFS, GIT (I think - if not there now it is due soon) and if necessary to a file folder. If you are not source code controlling your database objects, you need to.

No Affiliation, just a happy customer. You can download a fully working trial version (2 weeks) but honestly if you have ever had to generate a migration script the £1500 subscription is money well spent. What took me 4 days by hand the tools did in 4 minutes (and they did a better job: I had missed some view rebuilds after the source table schemas had changed, the scripts are well annotated and fully managed within transactions for rollback on failure)

Plus Redgate supply the resources to run this site, so they deserve our support.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 5

n.heyen (2/29/2016)
The development team is asking me to refresh data from Prod to Test without overwriting any stored procedures they might have on Test. So before I tell them no, is there a reasonable way to do this?
I've thought about trying to setup a job using sp_MSForEachTable to truncate the table in Test, then another sp_MSForEachTable to SELECT INTO to copy the data from Prod to Test.
Any one have ideas or a script they are willing to share?
Thanks,
Norman

Snapshot Replication