I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are running mirrored Intel 910 SSDs. These drives will present us with 4x 200GB partitions.

To make this work, we will need to split the single .MDF into 4 smaller ones using DBCC SHRINKFILE with EMPTYFILE. We have done this in test, and it still takes ~ 3.5 hours to do which is too long. The existing database is OLTP, and 365/24/7 and I know blocking will occur during this process, so we can't do it on production first.

My question, is there a way to backup and restore the database to the new server in a temp location. create the new files, EMPTY the temp .MDF into the new locations, then apply transaction logs after? That way we can move the data while current old production is up and running, then do a short shutdown, apply logs, and bring up the new DB?

Or are there any other options to get from Server A with one file and Server B with 4 files on different drives with minimal downtime?


3 Answers 3


In a word, no. You have to do it live.

What you'll want to do it add 3 new files. Then simply start doing index rebuild operations. As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files.

I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.

  • Yes, running enterprise on the new setup. I can do some testing, do you know if there is a speed difference between rebuilding all the indexes vs just emptying the primary file? I have a feeling that may be slower since our indexing is a little out of control. That is an issue I will tackle after we get off the old servers.
    – Lauren
    Mar 15, 2013 at 0:57
  • You'll have to do all of this on the old server. Is the old server standard or Enterprise?
    – mrdenny
    Mar 15, 2013 at 16:18
  • old is only standard, so no online rebuilds
    – Lauren
    Mar 17, 2013 at 19:07
  • 1
    How is an index rebuild supposed to help move from server A to server B? The question describes a server switch, not a storage switch. Mar 18, 2013 at 0:13
  • I am pretty sure for the index rebuild to work I would need to move the data to the new server first, either through detach and copy or via backup and restore. I would then need to either shrink the old primary file via DBCC to the new files, or move the data via a index rebuild.
    – Lauren
    Mar 18, 2013 at 3:50

I know this question is rather old, but I had a similar situation during a migration so I had to comment.

What you could do is

  • Set up the new 2012 database as an empty shell on your new box.
  • Ensure you have all of your file groups setup as you would like (striped across several disks and allocated to same sizes).
  • Script all your schema objects from the 2008 database and apply them to an empty shell of the new 2012 database (ensuring you apply triggers/indexes, all objects during scripting wizard).
  • Setup transactional replication from source database to destination for all tables which have primary keys (set articles to truncate only if objects exist).
  • For other tables that don't have primary keys, setup snapshot replication.
  • Add the destination database as a subscriber to both the Transactional and Snapshot publications.
  • Run snapshot agents for tables in transactional replication, then ensure your distribution jobs complete and data is flowing.
  • During the migration have the app shutdown, then run the snapshot agent/distribution agent for the Snapshot replication publication.
  • Then have app team bring up the app, run smoke tests, then UAT tests etc.

Of course, run this is UAT or another non-prod environment first :) Once testing is complete, then you break down the replication setup and your data remains on the new server.

The advantages of doing a migration in this manner as opposed to BCP'ing your data across are

  1. Source database stays available as long as possible
  2. all dependencies are taken care of by snapshot process
  3. data migration is repeatable without much changes.

The only caveat to watch after are any tables setup for CDC or trun/load tables on source.


I would set up striping in the operating system or hardware. That will be easier to manage than multiple files in SQL Server.

Can you create a hardware RAID 1+0 across all 8 SSDs? Can you do it in software, using Windows storage?

Otherwise you could create a new database using 4 files then use either the Database Object Copy Wizard or Transactional Replication to move the objects from old database to new database. I'm not sure how the duration of this will compare to the 3½ hours for splitting. Since these methods will lead to split-brain you will have to have some down-time while the objects copy across (the strategy is, disable updates to the old server, allow enough time for all the new data to have replicated to the new server, test data integrity, enable updates on the new server).

  • Sadly, that is the downfall of the Intel SSD, no hardware RAID. Some/most of the FusionIO cards are the same from what I have read. To make them fast, they basically connect the SSD controller directly to the PCIe bus, then you have to deal with volumes at the OS level. Each card presents us with 4x 200gb drives, so we are using windows software mirroring across cards to have 4x mirrored volumes we will be storing our data on. I still have not bench-marked the other options as some other fires have started I need to put out first.
    – Lauren
    Mar 18, 2013 at 3:47
  • 1
    Using the database copy wizard or replication are horrible approaches as data will be missing on the destination database if the database copy wizard is used, and with replication you'll be missing users, permissions, roles, etc that don't get copied across.
    – mrdenny
    Mar 18, 2013 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.