Splitting a large SQL Server MDF file I have a large (1.2 terabyte) SQL Server database that I need to migrate to a new server. Most of the database lives on a single, 1.25 TB data file, and a little bit sits on a much-more-manageable 550 GB file (which is practically empty).

Now, the tricky bit: the server to which I'm migrating only has 3 700 GB volumes, meaning I need to somehow dissect this goliath into three equal chunks. Most advice I've found involves creating 3 target files and running DBCC SHRINKFILE EMPTYFILE on my main file to empty it into the targets, but that'd take ages with a database this large.

Is there a recommended method for splitting a database this large? I'm considering using the Sql Server Integration Services Data Export feature to dump the data into a clone database with the proper file structure, but I'm curious as to whether there's a better way.
DBCC SHRINKFILE -- will be single-threaded – which will contribute to the long run-time.

Also, Sql Server Integration Services Data Export will be slower due to massive database size (1 TB) !

Instead, you should look for BCP OUT (in binary format) and BULK INSERT in the database.


BCP can read the SQL Server native format from text files. This is a very fast option that requires minimal parsing of the text file input.
The BULK INSERT command is the in-process method for bringing data from a text file into SQL Server. Because it runs in process with Sqlservr.exe, it is a very fast way to load data files into SQL Server.
Additionally, enable Trace Flag 610 to minimally log inserts into Indexed tables along with BATCHSIZE and ROWS_PER_BATCH parameters.
You can even disable Lock Escalation which on sql server 2005 generally occurs at 5000 locks allocated. This can be disabled on sql server 2005 by setting a batchsize to a number lower than this or enableing Trace flag 1211 (to completely disable lock esclation). In sql server 2008, you can do it using alter table  &lt;table_name&gt; (LOCK_ESCLATION = DISABLE)
BCP has a -a switch to allow to specify packet size or if using SSIS it can be done in properties of the connection manager --> PacketSize = 32767
To avoid  PFS contention, consider using -E startup parameter.


Also at hardware level, you can look into


Use fast NIC and switches. 
Have the latest certified drivers for your NIC. 
Enable full duplex. 
Enable support for Jumbo frames. 
Use TCP Chimney Offload. 
Use Receive Side Scaling (RSS).


Check the BCP &amp; BULK INSERT script that I provided at here 

Note: Your hardware including disk partitioning, layout, number of CPU's, NUMA configuration, etc will also play a big role in performance when you load your data.

Excellent reading:


Bulk Inserting 1 Terabyte within 10 minutes
We Loaded 1TB in 30 Minutes with SSIS, and So Can You
Load 1TB in less than 1 hour
The Data Loading Performance Guide