Migrate SQL Server database to multiple files [closed]

Question

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 SHIRNKFILE 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?

Answer 1

One think you can do, if you have the disk space, is:

• Restore the database on the new server
• Create a new file group
• Create all the indexes on the new file group with DROP_EXISTING=ON (and specify the new filegroup)
• Drop the old file group

This won't work for Large Object Data, you would need to move that manually to a new table.

Syntax for this would be something like:

CREATE CLUSTERED INDEX [index]  
ON [schema].[table]([columns)
WITH (DROP_EXISTING = ON, ONLINE = ON) 
ON [filegroup]

It is possible that the index can stay online while the new one is created. This will cause the tempdb to use more space though.