Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to merge SQL 2005 database files


How to merge SQL 2005 database files

Author
Message
cheungh
cheungh
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 157
Hi:

I come across a hugh database that the database files was created by previous owner, so the database files would be xyz.mdf, and xyz.ndf1, xyz.ndf2, and zyz.ndf3 and so forth ..

How would I make all these database files back to just ONE single xyz.mdf

How to merge all these secondary data files into one primary database file

Anyone know, please help.

Thanks
SQLdevotee
SQLdevotee
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 568
Try using OLE DB source>>Union ALL>>OLE DB Destination

*OLE DB Source will contain ALL your Database tables that you want to bring in

Imagination is more important than knowledge-Albert Einstein
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47208 Visits: 44367
Probably not with SSIS.

Are there different filegroups, or are all the files part of the same filegroup?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


cheungh
cheungh
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 157
This are the same database, but previous database creator created the databas using more than 1 file, the files are in the same file group, so they are seating at the drive with 1 mdf, and many ndf, but they are one logical database with many phyical files in 1 file group.

Now need to make it back to just 1 xyz.mdf file

What happened is Microsoft educated them if the database is too big, make more database files and scatter over many drives for performance, but this is not the case here.

It actually cause issue because all files are seating in one drive and hard to migrate to other server, due to many files.

I like to find a way to reverse the many database files to back to the default 1 database file mdf

This is like MS Access onl have 1 mdb file, but now cut it into 4 files, how in SQL do they have some command to bring all those database files into 1 database file.

I try to backup and restore, still restore to many database files, the structure did not changed.

I was thinking there may be some command say DBCC move database and some option in the move process convert the many database files back to 1 database file, they are in the same file group.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47208 Visits: 44367
Since they are the same filegroup, you probably need to run the DBCC SHRINKFILE with the EMPTYFILE option.

EMPTYFILE migrates all data from the specified file to other files in the same filegroup. You can then use Alter database to remove the empty files.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


cheungh
cheungh
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 157
Thank you very much for all your help.

I will make a backup and restore to another machine and test it out.

1) backup and restore to another sever
2) shrink the file group
3) do the empty to see if empty will make all secondary database files to merge to a single xyz.mdf file
4) create a new xyz.log file for the database

I will focus on the empty procedure.

Any links to the command empty using DBCC.

Thank you.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47208 Visits: 44367
cheungh (2/21/2008)

4) create a new xyz.log file for the database


No need to do anything with any logs if you're emptying data files


Any links to the command empty using DBCC.


Books Online has a small section.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


cheungh
cheungh
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 157
Thank you, I will try, thanks a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search