What's the best method to refresh only a few tables within a test database from production?

Question

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.

Answer 1

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

Answer 2

There is no need to do backups and restores, or call / coordinate external processes (i.e. BCP), or even mess with SSIS (very powerful, very cool, but if I can avoid it, I certainly will :). You can handle all of this from the comfort of T-SQL, in a stored procedure that you can schedule via SQL Agent, or a script that you run once a month (though having it in a proc and scheduling is less work in the long run). How? By using SQLCLR to access the SqlBulkCopy Class in .NET as it is essentially BCP without all of the fuss of calling BCP. You can code this yourself: there is no super complicated setup or anything as the SqlBulkCopy class takes care of nearly everything for you (you can set the batch size, whether or not to fire triggers, etc). Or, if you don't want to mess with compiling and deploying an Assembly, you can use a pre-built SQLCLR stored procedure such as DB_BulkCopy which is part of the SQL# SQLCLR library (which I am the author of, but this stored procedure is in the Free version). I describe this in more detail, including an example of using DB_BulkCopy, in the following answer:

Import data from one Database to another script

If it is not clear where to place this in your current plan, you would do the following:

• Remove steps 1 and 2 (woo hoo!)
• Replace step 5 with an EXEC of DB_BulkCopy or whatever you call it if you code it yourself, which just moves the data from Point A to Point B.

Also, it should be pointed out that SqlBulkCopy and DB_BulkCopy:

• can accept any result set: doesn't matter if it is a SELECT or EXEC of a stored procedure
• are very easy to update when schema changes are made to any of these tables; just ALTER the query in your Stored Procedure that calls this SQLCLR Stored Procedure
• allow for remapping the fields, if that is ever needed

---

UPDATE Regarding Minimally Logged Operations via SqlBulkCopy

It is possible to get minimally logged operations, but you should know:

• You need to use the TableLock Bulk Copy option
• Doing this will, for tables with Clustered Indexes, first load the data into [tempdb] and then do the ordered insert into the destination. Hence, there is some additional load incured, both in terms of the physical I/O to tempdb (data and log files) as well as the sort operation (due to the ORDER BY which is required to get the minimally logged operations)
• Some testing results here: Whitepaper: performance of SqlBulkCopy
• Some testing results here: Troubleshooting SqlBulkCopy not doing minimal logging