Sql-server – What’s the best method to refresh only a few tables within a test database from production

sql serversql-server-2008

I have a very large production database and a very large test environment database in SQL Server 2008R2. Both databases have similar table structure but different users/logins/permissions/roles.

I need to refresh only a few tables in the test database periodically from production, about once a month.

The current way I am planning on doing this is

  1. Use the BCP utility to take an export of the tables I need from Production.
  2. Copy the bcp export file onto test server
  3. Disable indexes and constraints on all tables I am refreshing in Test
  4. Truncate the Test database tables
  5. Load Data back into Test database tables using BCP.
  6. rebuild indexes and re-enable constraints in Test

This all seems a little too complicated for such a small task. It also seems like it would generate a lot of redo (in the t-log) Is there a better way to do this?

Another way I thought of doing this is to restore a backup from Production onto the test environment–but the issue I have is that a full backup would be quite large and I don't need all tables to be refreshed, only a few–and also the users and security in the production database is different from test. That would be overwritten by the security settings in the production database if I restore the whole database.

Best Answer

There are 2 methods that will suit your needs :

(Note: If the tables are referenced by foreign key, then you won't be able to use TRUNCATE. You have to delete in chunks. Alternatively, you can drop all indexes + Foreign keys and load data and then recreate them).

  • BCP OUT and BULK INSERT INTO destination database.

    • Make sure you put the test database in simple/bulk-logged recovery mode.

    • Enable Trace Flag 610 - minimally logged inserts into indexed tables.

      /************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)

--

  • Method 2 : SSIS - My preferred method in this case.

    • No staging to disk required. All processing is done in memory.
    • You can schedule the SSIS package using sql agent job every month to automate the refresh of tables from PROD to TEST server.
    • Choose the "FAST LOAD" option
    • Make sure you choose a good rows per batch number (If you choose too high,there will be lock escalation - keep it lower than 5K)

Reference : The Data Loading Performance Guide and my answer for - Insert into table select * from table vs bulk insert

Related Question