Having production environment data in quality environment using SQL Server

Nuno Fernandes

Staff

Question

Hi all,

One good way to reproduce with increased fidelity a production environment in a quality environment is having its data!

If you want to have a complete image of production the best solution will be making a database backup in production and restoring it in quality (for whch you can follow the recommendations in this nice topic https://www.outsystems.com/NetworkForums/viewtopic.aspx?TopicId=3471).

But if you only want to copy a couple of tables and not the whole database, but still keeping their referential integrity (foreign keys still referencing existing id's) then here's an alternative.

Pre-requirements for using the procedure described below:
. Confirm that you can lose (from quality env.) all the tables you want to copy from production.
. Make sure you have all the eSpaces (where the tables you want to copy are defined) available for publishing, in order to recreate the data model in the database.
. There's a server link between quality to production with enough permissions granted to execute the SQL script mentioned below.

1. Select the group of tables you want to copy making sure that you also include all tables that are being referenced by the ones you want to copy and with which there are referential constraints - use "Check dependencies" database management tool option on the target tables (or simply because you don't want to lose those data references).

2. Before this step recheck all dependencies of the tables you've just grouped and confirm there's none missing (you can't drop a table if other(s) depend on it, you must drop these first. After confirming the tables group, drop all of them (in quality environment, not in production! ;)).

3. For each table (ordering them so that tables that others depend on are processed first) execute the following SQL script:

SET IDENTITY_INSERT [<quality database>].[dbo].[<table name>] ON
INSERT INTO [<quality database>].[dbo].[<table name>] (<list of fields to copy>)
SELECT <list of fields to copy>
FROM [<server link to production>].[<production database>].[dbo].[<table name>]
SET IDENTITY_INSERT [<quality database>].[dbo].[<table name>] OFF

4. You're done!

Other alternatives include using SQL Server DTS.

21 Jun 2007

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines