Our Network Admin has setup a MS SQL 2012 server and wants the DBs setup so the database files are located on 4 different drives.

My problem here is when I try to restore a DB from a different system that only uses a single database file, it wants to bring the old file structure over with it. In the restore options, I can see the file structure it wants to use (1 database file), but I cannot change it.

Is there a way to restore the database into a new database without losing the file structure?

Thanks in advance. Richard

  • What advantage there would be with multiple data files ? have you though of any. Are multiple files going to be on same logical drive. If so there is very little advantage. Plus writing to data files is in round robin fashion – Shanky Sep 8 '14 at 8:19

You could just restore as is and Add files after restore. To Add files, right click on the database and go to Properties, then files and add.

| improve this answer | |
  • I assumed I could do that, but by doing that, would it actually reorganise the data over the other files? Thanks – Richard Portelli Sep 8 '14 at 4:43
  • It will use a proportional fill see this article: (technet.microsoft.com/en-us/library/ms187087(v=SQL.105).aspx ) So no it won't automagically write to the new files. To do that, you may consider rebuilding clustered indexes and letting it rebuild the tables across the new files. But you still need to create the files first. You may want to consider adding filegroups - one on each disk, then adding files to that. You can then assign tables to the filegroups. This will take manual intervention, but it will allow a lot of control and space management. Then isolate heavy used tables. – Brian Nordberg Sep 8 '14 at 19:16

If your current database is made up of one data file and one log file then that is how it will be restored.

What purpose does the network admin in doing this if the current database is one single data file already? Does network admin understand how file structure works in a database?

If you just wanted to split the tables and indexes between multiple files you would need to manually create each in their own file group, assigned to its own physical file. Then you could import the data into those tables. The other way is to do that same thing after you restore it.

Outside of that if you involve partitioning tables between files it becomes more cumbersome and involved.

| improve this answer | |
  • Hi Shawn, Thanks for that. I assume by partitioning you are referring to having multiple MDF files for the one database? – Richard Portelli Sep 8 '14 at 1:59
  • @Richard No, partitioning doesn't necessarily involve having multiple MDF files. You can partition without using different files and filegroups, and you can use multiple files without partitioning. – Aaron Bertrand Sep 8 '14 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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