0

I am developing a Grails-application which uses several databases, others are read-only and 1 is the app's sort of a "main db". Additionally there are multiple environments: dev, qa, prod. qa is used for release-testing and is identical to prod.

Always before release-testing I need to overwrite the "main" qa-database with "main" prod-database. I don't have other than SQL-user access to the server running MS SQL instance.

What I need is the magic that drops everything in qa-database without dropping the database itself and imports everything from the prod-database. Databases contain a lot of foreign key constraints.

How to achieve the aforementioned?

P.S.

I did this on MySQL but now we've migrated to MS SQL. My MySQL-script goes somewhat like this (pseudo):

SET foreign_key_checks = 0;
-- Drop all tables..
SET foreign_key_checks = 1;
-- Import prod-dump to DB..
heikkim
  • 2,955
  • 2
  • 24
  • 34
  • possible duplicate of [SQL SERVER 2005: Drop all the tables, stored procedures, triggers, constriants and all the dependencies in one sql statement](http://stackoverflow.com/questions/536350/sql-server-2005-drop-all-the-tables-stored-procedures-triggers-constriants-a) – Michael Durrant Nov 17 '11 at 13:06
  • @MichaelDurrant - he's looking to recreate all the objects as well, so it's not a dupe of that question. – JNK Nov 17 '11 at 13:13

2 Answers2

0

You shouldn't do this in straight T-SQL.

You really should use something like SMO Scripting in .NET to export objects in this way. There is NO clean way to do what you are asking in pure SQL code.

There are too many variables to account for if you plan to just build dynamic SQL from system tables, which is the only way to approach this in T-SQL.

JNK
  • 63,321
  • 15
  • 122
  • 138
0

I think the the tool "xSQL Data Compare" exactly matches your requirements. You will need "sa" access at least for the qa-DB though.

huo73
  • 599
  • 6
  • 17