Sql-server – Migrate existing SQL Server database file to smaller files

database-designsql server

I have a database that is about 1TB in size. It's currently on a 2TB hard drive and I would like to replace it with a few 512GB SSD. I need to split the existing database file (.mdf) into multiple smaller files with file size limited at just below 512GB each.

Ive been doing some research online. It is not clear to me whether I should add filegroups or just add files to my primary filegroup.

How do I move data from the existing file (1TB) to the smaller files (512GB) safetly?

Has anyone done anything similar?

Best Answer

Just adding files to the existing file group will not redistribute the existing data.

I would go the route @Max is suggesting and ensure that the new files you are creating are the same size to maximize the proportional fill feature. And before you begin take a full backup.

Once you've migrated all of the tables, indexes, etc over to the new file group you should be able to shrink the mdf down to be under the 512GB point or smaller if you are not going to use the default file group anymore.

At that point take another full backup and then restore that to the new disks with the MOVE TO option in the restore command. This will let you take the new ndf files in the new file group and put them across the SSDs.

Depending on how you are managing the database you might want to set the new file group to be the default for all of the new objects that are created. Otherwise they will still default to the old file group and since it only has one file in it the objects/data in it won't benefit from the proportional fill feature that SQL Server has.

Moving file groups reference