SQL Server Performance Forum – Threads Archive

Migrate database from single file to multiple file

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! RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

1. Restore database onto new server, with "MOVE" files in correct location but single data and single log file. 2. run ALTER DATABASE ADD FILE etc… to add new data file with requisite file growth and maxsize attributes. 2. Run ALTER DATABASE MODIFY FILE… etc to set requisite file size and growth characteritics of existing data file (& log file if necessary). Then let SQL Server take care of spreading the data into the new file, it’s very good at it. Is this what you mean? Tom Pullen
DBA, Oxfam GB

I feel Tom’s approach is ideal and you can only use ALTER DATABASE to divide the data files and using SP_ATTACH_DB or RESTORE … WITH MOVE option will take care of seperation of data and log files, books online is a good start about these statements. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks to all who have given a quick solution for me! But what will happen to the single 100 gig data file, after sql server spreads the data in other 2 gigs new data files? RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Well nothing will happen to it? It’ll still be there, I assume… Tom Pullen
DBA, Oxfam GB

I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool
Download Free : http://www.dbload.com

]]>