Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
Thanks in advance for all help.
We are building a new SQL Server 2008 prod box. I need to take the data from the current prod box and restore on the new one.
I would like to get the databases set up better so we can have the database files over multiple volumes but creating multi volume file groups. But i beleive when i restore it will only allow me to restore the 1 mdf from the backup to 1 mdf on the restore side.
What is the suggestions to do this? Is there one?
Thanks again Rich
You can't do it from a backup but you can generate scripts from your existing server. Then modify the scripts in the create database statement to add the new file group name. After that, just run the scripts in the new server. This will create the databases with new filegroups. Then export your data from the existing server and import into the new one.
The trick is to do it without having to drop all the foreign key constraints!
There is a script out there, but the comments are in German. If you've got some experience in SQL Server, you should be able to figure out what it does & use it for your needs accordingly.
You won't be able to do this on the restore. After you restore, you will need to move the clustered indexes to your newly created filegroups with multiple files. Make sure all your files are the same size and growth rate and make them large enough to not need to grow after you move the tables (clustered indexes). Remember, where ever the clustered indexes are, so is the table. Nick is right, the PK/FK relationships will need to be recreated.
