Caveman's Blog

My commitment to learning.

SQL Server: How to copy a very large table?

with one comment


This blog is an extension to my earlier post on how to copy a table. This post is specific to the situation where you have to copy huge  amount of data from your source table and to a destination table as quickly as possible. The amount of time to copy and the explosion of the log file are the two points of concern during this activity.

Let us look at a table with large amounts of data. We can use sp_spaceused on a table to find out the physical attributes. The following screen-cap tells us that the faded out table has close to 7 millions rows of data with the total storage size of 37 GB approximately.

It took more thank 10 hours to copy the data in the source table to a destination table in another database of the same SQL Server instance via the using standard Insert-Select Transact-SQL statements. The byproduct is this operation was large log file. The solution to deal with this issue would be the one where the copy action is not logged at all or is logged at the minimum. This would reduce the I/O and will speed up the copy process.

Bulk_Logged Recovery mode (plus “Select Into”) and BCP Utility are the two tools that can be used to achive faster copying times with no or minimal amount of transaction logging.  Following is the usage of technique # 1 via using the SELECT Into clause in tandem with the Bulk_Logged recovery mode to copy the same table. The first step in this process is to programtically or manually set the database recovery mode to “Bulk-logged”

--Programatically setting the Recovery mode
USE master;
ALTER DATABASE DemoX SET RECOVERY BULK_LOGGED;

You can manually set the recovery mode by accessing the Database properties dialog box:


Step 2 is to actual copy the data via executing the “Select Into” Transact-SQL. The last step in this process is to reset the database recovery mode to “Full”.

select * into DemoX.dbo.Employee from Demo.dbo.Employee

USE master;
ALTER DATABASE DemoX SET RECOVERY SIMPLE;

Copy Time: 2.75 hours. This is pretty decent considering that the traditional copying took more than 10 hours to do the same job. My next goal is to use the BCP utility to copy the same table and compare the timings. Here is the basic idea of using this utility

Step 1: Export data to a file using the BCP utility.
Step 2: Import data into the destination table using the BCP utility.

Will post my results soon.

References:
1. MSDN Online – Bulk Logged Recovery Model
2. MSDN Online – BCP Utility

Advertisements

One Response

Subscribe to comments with RSS.

  1. […] SQL Server: How to copy a very large table? […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: