Synchronize the data between the databases in SQL Server

Download the code

We can use database backup/restore but this will overwrite the development database which includes the schema, stored procedure and all other objects which most of the developers don’t want to.

There are 2 approaches to do this :.

1- SSIS  
2- Automated T-SQL Scripts

T-SQL

SSIS

One time automated script will be suffice which can be used for n no.of databases

Specific to one database. Requires many data flow connections

Easy to troubleshoot and can be controlled when the development schema (target) changes.

Bit difficult to troubleshoot the data flows when it fails at multiple data flows due to schema change at destination

Ok, now we are going to do with automated T-SQL Scripts.

If the database has foreign keys (of course mostly) then there are 2 ways to do this.

1. Insert the data sequentially without dropping the foreign-keys
2. Drop and recreate all foreign keys before and after data load

Download the Attached Zip file which has 4 procedures and 1 funtion

Object Name

Description

dbo.PR_DATA_REFRESH_SEQUENTIALLY

Identifies the tables in Hierarchy

dbo.PR_HIERARCHIAL_DATA

Deletes all rows in Destination (QA)and Populates the Data with Source(Production)

dbo.PR_RESET_IDENTS

Resets the identity property at the destination tables after insert

dbo.fnColList

Function to produce the list of  columns ,except Computed columns. will be used by the above stored procedures to prepare insert into statements.

This Approach doesnt requires Drop and Recreate Foreign Keys, which improves the performance of the load.

You can modify the same scripts to extract the subsets of data (10% of data) and this approach helps to check any relational integrity errors.

To test this automated scripts in your environment. you can execute the script "SIMULATE.SQL" which creates a database D1 with set of empty tables from Adventureworks.

Steps :

1. Create a Linked Server from Destinatation to Source Server with Read Only Permission to avoid any problems with the wrong parameters.
   Its strongly recommended to restore  the backup of the production database to Dev/QA and then refresh from the restored database
2. Create all the objects (download) in the destination database (QA/Dev).
3. Execute the PR_DATA_REFRESH_SEQUENTIALLY stored procedure with the source and destination database names.
   EG:. exec PR_DATA_REFRESH_SEQUENTIALLY  <myLinkedServer.SourceDB> , <Local destination database>
4. Review the output which populates the target database.