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
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.
Insert the data sequentially without dropping the foreign-keys
Drop and recreate all foreign keys before and after data load
Download the Attached Zip file which has 4 procedures and 1 funtion
Identifies the tables in Hierarchy
Deletes all rows in Destination (QA)and Populates the Data with Source(Production)
Resets the identity property at the destination tables after insert
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.
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
Create all the objects (download) in the destination database (QA/Dev).
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>
Review the output which populates the target database.