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?