Copy Data from PROD to DEV

PROD DB -> Localhost DB -> DEV DB

• Create database on Localhost
• On Localhost, create Linked Server to PROD DB
• Copy data from PROD DB to Localhost DB
• Once the data is in Localhost, create a SQL backup of the Localhost database
• Copy the backup file to DEV DB and restore the database to DEV DB
• Copy the data from restored database to DEV DB

Note:

• Make sure you disconnect from PROD after you have copied the data from PROD to Localhost.
• Make a backup of the database in Localhost and restore it to the DEV DB. Then copy the data from the restored database on DEV DB to the destination database on DEV DB. This will be much faster.
• Do record count of before and after.