Migrate database from single file to multiple files

• Viking

  SSC Rookie

  Points: 28

  More actions

  December 10, 2004 at 4:28 am

  #109775

  Hello All

  I have be assigned of moving a database from one production to other production box.

  I need to follow new conventions laid per the Mgmt team, that I need to have data files split up in different drives raided differently. Say .MDF files should be in G drive, .LDF files should be in E drive..etc. In one of these laid conventions I need to have all new databases migrated or newly created to be having multiple files with MAXSIZE of 2 Gigs, FILEGROWTH OF 100 Megs.

  Now the trick is that I need to have an existing database restored (moved) from old production box to the new, such a way that I have multiple files..per above conventions, of the restored database. But, the database on the old box has present structure of a single .MDF file. Now, when I do the restore operation I will have logical file with single .MDF. I am aware of one of few methods that I need to create a database with new conventions & import data to the destination database from source. I am sure, this would be last option for me as the database has data of 100+ Gigs.

  Can anybody out there help me with any other method on taking care of this?

  Thanks in advance!

• Johan Bijnens

  SSC Guru

  Points: 135551

  More actions

  December 10, 2004 at 5:12 am

  #533306

  IFAIK you can only restore 1-1 (logical name vs filename) using restore database.

  What you could do is :

  - restore the full database to its new server

  - put the restored db in simple recovery model.

  - add as many filegroups as you need, containing each a number of files with maxsize set to 2 Gb.

  Provide the correct number of files per group because sqlserver will balance the contents !

  Then alter the objects so they reside in the new filegroup(s). 

  At the end, you could use DBCC SHRINKFILE [check BOL] to shrink the 100Gb file to its appropriate size.

  

  I hope this helps.

   

   

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me