Synchronize the data between the databases in SQL Server

Refresh Data in SQL Server
Download the code:
DataRefresh.zip

Data refresh is one of the most common DBA ACTIVITIES. As a DBA I often asked to refresh only the data without modifying the schema on the target database.Devolopers might need the data from the production into the development to test and debug the application.

If the task is to refresh the Database (includes schema) then we can take the backup of the database and restore it on the development database, but if the task is to move only the data then we have to do it through SSIS or T-SQL scripting with Insert statements.

When compared to SSIS and T-SQL scripting, I prefer T-SQL scripting as if you have to do this on regular basis and if the destination schema got changed then the SSIS package will fail and the time consuming to develop an SSIS package for each database refresh will be more when compared to an automated generic T-SQL scripting which will work on any database without spending any efforts on developing it.

As a DBA, I strongly feel that the automated script to refresh database data should be in place as this is the most common requirement and allows the DBA to generate the script to refresh the database quickly.

If you were to do this Data refresh manually, you would probably follow either of the below approaches (A or B) contains the steps more or less:

Rows highlighted with blue background in the below table has the same steps in A & B.

	A. Through with Out Disabling Foreign Keys	B. Through Disable Foreign Keys
1.	Identify the list of tables in hierarchy to delete the data to avoid the errors occurred with foreign key violations.	Create the T-SQL scripts to Drop  All Foreing Keys Create the T-SQL scripts to Create All Foreing Keys
2.	Delete the data from the tables sequentially which are identified in step 1(we can’t truncate a table ,if it contains the foreign key even if it’s an empty table)	Execute the script to Drop All Foreign keys from Step-1(a) b. Truncate all the tables.
3.	Identify the list of tables in hierarchy to insert the data into primary key tables first without having any problems with foreign key violations.
4.	Generate the insert into statements like‘insert into target...table select ,, from source..table’. [Include all the column names in the insert into..  statements as its required specifically for the tables contains identity property]	Generate the insert into statements like ‘insert into target...table select ,, from source..table’. [include all the column names in the insert into.. statements as its required specifically for the tables contains identity property]
5.	Append the statement ‘set identity insert ’ before and after the insert statement for the tables contains IDENTITY columns.	Append the statement ‘set identity insert ’ before and after the insert statement for the tables contains IDENTITY columns.
6.	Identify the columns with TIMESTAMP data type and modify the insert statement to insert null values in the timestamp field as SQL Server won’t allow inserting the data into a TIMESTAMP column with the data from another table.	Identify the columns with TIMESTAMP data type and modify the insert statement to insert null values in the timestamp field as SQL Server won’t allow inserting the data into a TIMESTAMP column with the data from another table.
7.	Execute the insert statements	Execute the insert statements
8.	Reset the identity values in the target tables with the source table current identity property.
9.		Execute the script to create all foreign keys from step-1(b)
10.	Execute the attached 3 scripts which will create 2 procedures and 1 function on the target database where you want to refresh the data. PR_DATA_REFRESH_SEQUENTIALLY.SQL.TXT PR_HIERARCHIAL_DATA.SQL.TXT FN_TABLE_COLUMN_LIST To refresh the database: Open the Query Analyzer on the Target Database. Press Ctrl + t [Review and Execute the output of the below query on the target database] Use < Source Database Name > GO Exec PR_DATA_REFRESH_SEQUENTIALLY	Execute the attached 2 scripts which will create 1 procedure and 1 function on the target database where you want to refresh the data. PR_DATA_REFRESH_SQL FN_TABLE_COLUMN_LIST To refresh the database : Open the Query Analyzer on the Target Database. Press Ctrl + t [Review and Execute the output of the below query on the target database] Use < Source Database Name > GO Exec PR_DATA_REFRESH

Choose the best approach suits to your environment:
Approach : A	Approach : B
Choose this if you don’t have the alter table permission	Requires Alter Table Permission
Will take long time to execute as it contains foreign keys	It’s fast when compared to Option as it drops the foreign keys before refresh the data and will recreate after the refresh.