migrate data from production to development db

Question

Sometimes I need to copy interview bookings made on our production server to test some I have the same data in order to reproduce bugs / demonstrate features with live data.

I know the easiest way is probably to restore the a backup of the live server but its not always possible. Takes ages to restore on my crappy pc. People arent happy when I copy a 3gb file over the network.

I was wondering is way I can write a query to copy the relational data over from a readonly copy of production to my dev db.

for example table a has a relation with b. Table B may also have relations to other tables. I want to copy the data from table A and also have any related records copied as well.

we use sql server 10.50.1600

Answer 1

First and potentially the easiest option available: Flash drives are dirt cheap. Literally, you can get a 4gb flash drive for less than top soil.

If you do not have physical access to a USB port then install BIDS or SSDT on your dev machine if you do not already have installed and create a simple Source to Target SSIS package. You need login access to the separate servers and you can copy individual tables as needed. Eliminates cross server queries/linked servers and can minimize your network use to only necessary data.

Follow a standard BI staging table package for optimal results: Steps

1. Truncate destination table
2. Extract data from source
3. Write to destination

If you have table constraints I would think any legitimate testing would require them to exist in your test environment, but to make your data import as simple and quick as possible it may be advantageous to drop them temporarily, until you have all your needed data. The following scripts can drop and then re-create your FK constraints. (I've had it saved for a while but took it from a co-worker or blog, if anyone recognizes I'd be happy to update with a source credit.)

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200)    
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME 
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

--DROP CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   DROP CONSTRAINT ' + ForeignKeyConstraintName + '

   GO'
FROM
   @table

--ADD CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

   GO'
FROM
   @table

GO