Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.