# Transferring large amount (84 million rows) of data efficiently

I have about 84 millions rows. Of those all of them needs to be transferred to a separate database on the same server, then I delete to delete about 60 millions rows from the source database.

The 84 million rows are all in the same table. That table alone accounts for 90% of the whole database.

So... Source: 84 million rows -> 24 millions rows Destination: 0 rows -> 84 million rows

Source is running full recovery mode, destination will be running simple.

I am wondering what would be the most efficient way to do this?

Plan A:

1) INSERT INTO destination SELECT * FROM source

2) TRUNCATE source

3) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan B:

1) Restore a backup of source database as the destination database

2) Drop every tables except the one needed on the destination database

3) TRUNCATE source

4) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan C:

1) INSERT INTO destination SELECT * FROM source

2) DELETE source WHERE keep_condition = 0

or something else?

Thanks

• why don't you use Import and Export Data wizard? it is a tool provided with the installation of SQL Server. – Hani El Mouallem Sep 26 '14 at 9:08
• Is it possible to copy the 24 mil rows to a new table, then simply rename the two as needed so that you aren't ever moving 84 million rows unnecessarily? – LowlyDBA Sep 26 '14 at 16:25
• Is this a one-off or on-going process? I ask because, given the time it will take to process 80M rows, it is likely there will be data changes in SOURCE producing rows which should now live in DESTINATION. – Michael Green Sep 28 '14 at 11:22
• This looks like an XY problem: You need to end up with all 84MM rows in one DB, and 24MM of those in a second DB. What business requirement requires that 84MM be moved and 60M deleted, instead of just moving 24MM? link: meta.stackexchange.com/questions/66377/what-is-the-xy-problem) – Pieter Geerkens Sep 28 '14 at 21:30
• I have a very similar problem and it's clearly not XY. Prior to the proliferation of laws concerning record retention we kept all data. Now we must delete rows older than the date we are legally required to keep them. This means archiving and deleting over 20 years worth of data because legal retention in most cases is 7 years. I don't think I'm alone in believing Microsoft is remiss in not providing the 'bulk copy' functionality to stored procedures. An app should not be faster at data movement 'within' a DB than the DB itself. Next year another year must be archived. – bielawski May 6 at 14:50

I would add that, however you decide to approach this, you'll need to batch these transactions. I've had very good luck with the linked article lately, and I appreciate the way it takes advantage of indexes as opposed to most batched solutions I see.

Even minimally logged, those are big transactions, and you could be spend a lot of time dealing with the ramifications of abnormal log growth (VLFs, truncating, right-sizing, etc.).

Thanks

"Efficient" could apply to log file usage, I/O performance, CPU time or execution time.

I would try to achieve a minimally logged operation, which would be fairly efficient from a logging perspective. This should save you some execution timeas a bonus. If you have the tempdb space, the following might work for you.

CREATE TABLE #temp;
ALTER source -> BULK_LOGGED recovery model

BEGIN TRANSACTION;

INSERT INTO dest SELECT FROM source;
INSERT INTO #temp SELECT FROM source WHERE keep_condition=1;
TRUNCATE TABLE source;
INSERT INTO source SELECT FROM #temp;

COMMIT TRANSACTION;

ALTER source -> FULL recovery model
DROP TABLE #temp;


For a minimally logged operation to happen, a number of conditions have to be true, including no backups currently running, database set to BULK_LOGGED recovery mode, and depending on your indexes, the target table may have to be empty. Some of this behaviour also changed (improved) from SQL Server 2005 to 2008.

Then again, without knowing the specifics of your table and data, any of your other options may well perform better. Try using

SET STATISTICS IO ON;
SET STATISTICS TIME ON;


.. and see which works best.

EDIT: When performing bulk-logged operations, make sure you make a backup (full or transaction log) before and after the operation if you need point-in-time restore capability and you suspect that other activity may be going on in the database at the same time that your ETL job is running.

I wrote a blog post on minimally logged operations a while ago, there are links in there to other posts and documentation.

• +1 for advising OP to test to see which performs better. Of course, that might be a bit difficult to get real numbers unless (s)he has a duplicate system in dev, etc. – Max Vernon Sep 26 '14 at 13:34
• Just a question, What would happen if you try to do a point in time restore when the database was in bulk logged mode? I supposed any transaction that is not qualified as "bulk" would be recoverable. – elty123 Sep 26 '14 at 17:18
• @elty123 In bulk logged recovery you can only restore to then end of your last log backup. There is no point in time recovery like there would be with full recovery. Normally you switch to bulk logged recovery, run some ETL process, switch back to full and then take a log backup. – RubberChickenLeader Sep 26 '14 at 20:00
• @WindRaven This isn't correct - see my answer below. – wBob Sep 27 '14 at 6:30
• @wBob and @WindRaven, I've updated my answer to reflect the need to take backups before and after using BULK_LOGGED mode. Thanks! – Daniel Hutmacher Sep 27 '14 at 7:28

Why not BCP?

1. Back up the sourcedb
2. Change sourcedb to bulk-logged
3. Open command prompt

4. bcp server.sourcedb.table out Filename.flt -T -c

5. bcp "SELECT * FROM sourcedb.table WHERE keep_condition = 1" queryout Filename2.flt -T -c

6. bcp Server.destinationdb.table in Filename.flt -T -c -b1000

7. check the data

8. From SSMS Truncate the sourcedb table
9. bcp server.sourcedb.table in Filename2.flt -T -c -b1000
10. Change sourcedb back to full
• Because they're on the same server. Writing out to the filesystem would be expensive. Better to create a database and presize it, hopefully taking advantage of instant file initialisation. This would be a reasonable choice for dbs on different servers although SSIS would be my first choice if available. NB: Option -n (native) is more compact and safer for moving data from SQL Server to SQL Server. Option -b has no effect for bcp out. – wBob Sep 27 '14 at 6:22

Don't think you should be recommending changing the recovery model without either a full database backup or t-log backup before and after. One of the features of BULK_LOGGED recovery model is that you will lose the ability to do point-in-time recovery for t-logs containing bulk-logged operations. Classic scenario: nightly full backup, hourly t-log backups. You change the recovery model to bulk-logged and start your operation. Something goes wrong and the transaction rolls back (or you haven't used one). However you're not sure what else was going on in the database so you want to restore to a known good point.

When can you restore back to? Last hourly t-log backup that does not contain bulk-logged operations, potentially losing n minutes of transactions. A full backup or t-log backup before changing the recovery model will create a fallback point. Which one you choose depends on your RTO.

Dropping partitions out of a table is a really fast and resource-efficient way of removing large chunks of data from a table. Were this table partitioned in a manner that supports your source / destination split the answer would be to restore a copy, drop the redundant tables and redundant partition(s) from destination and drop the complementary partitions from source.

The cost of enabling partitioning may make this a more expensive operation overall, however.