Sql-server – Restore a single database backup (.bak) file into multiple filegroups

backuprestoresql serversql server 2014ssms

I have a SQL Server 2014 database DB01 (single filegroup – PRIMARY) and I took a backup of it into a DB01.bak file in SSMS.

I have another database DB02 with 1 primary filegroup (1 file) and one secondary filegroup (2 files). I am trying to restore the DB01.bak file into DB02 with multiple filegroups. Is it possible to do this in SSMS?

Best Answer

No it is not possible to do what you are asking. SQL is backing up the current state of the database. What tables are in what filegroup is part of the database information. It is possible to restore the backup to a new database name. Once the data is on the new server you could research and create the file groups you want, make your changes, etc. Get everything you the way you want it. Back it up a final time and then restore it to the database name you want or you can simply rename the database. Depending on the size it might be faster to restore it.