Restore Database with FileGroups

[crmayer]

Hello,
I have a production database that has multiple filegroups and I want to make a backup of this, and restore it to a different server for our development database. I am able to do this, but I want to merge all filegroups into the primary filegroup.

I have done a regular restore, then used the shrinkfile with the emptyfile option and was successful with getting one of the filegroups merged. When I try that on the 2nd file group, I am getting an error that there is not enough space in the file group to complete the emptyfile command.

Is there a way to backup the production database, and then just restore it to development and merge all filegroups during the restore process?

Or am I doing something wrong when trying to merge them manually? I have checked and I do have enough disk space to accomplish this task, so I am not out of disk space.

I also found somebody that suggested making sure there was enough space in the primary filegroup, and to turn off the autogrow option, but again, no luck.

I have searched and searched on this, and it appears that the shrinkfile should work, but nobody has an answer to the space error, although I do not seem to be the only one getting this error.

Any ideas or help would be greatly appreciated.

Thanks in advance.

 

[DrSql]

You approach is right but I would suggest to pre-allocate the required space to primary group and this should help your merge quicker. Also verify that the second file groups are in primary group or diff files? If it’s different file groups then you need to move the object to the primary filegroup. By default all objects get created on primary file group unless you specifically put on the additional file froups.

Please refer this links

http://i.msdn.microsoft.com/Ee377060.6be47c0e-06c3-45d9-bce2-a42453da7d19(en-US,BTS.10).gif

http://i.msdn.microsoft.com/Ee377060.d5d5c5df-d483-4f01-8128-f98228de51b9(en-US,BTS.10).gif

Dr.Sql
Good Luck.

 

[crmayer]

Sweet, let me try that...

Thanks DrSql, I will let you know if that works for me.

 

[crmayer]

Thanks for the help, but we ended up going another route, we are just leaving the two volume groups in hopes of making our development environment more like our production environment.

I will play around with this once I get another server setup though.

Thanks for all your help.