What's the best method to refresh only a few tables within a test database from production? 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


Use the BCP utility to take an export of the tables I need from Production.
Copy the bcp export file onto test server
Disable indexes and constraints on all tables I am refreshing in Test
Truncate the Test database tables
Load Data back into Test database tables using BCP.
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.
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 --&gt; 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 &lt;&gt; 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) &lt;&gt; '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 &lt;&gt; 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) &lt;&gt; '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