Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to merge SQL 2005 database files Expand / Collapse
Author
Message
Posted Thursday, February 21, 2008 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:44 AM
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
Post #458542
Posted Thursday, February 21, 2008 11:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:39 AM
Points: 214, 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
Post #458716
Posted Thursday, February 21, 2008 11:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #458723
Posted Thursday, February 21, 2008 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:44 AM
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.
Post #458730
Posted Thursday, February 21, 2008 1:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #458778
Posted Thursday, February 21, 2008 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:44 AM
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.
Post #458831
Posted Thursday, February 21, 2008 10:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 41,558, Visits: 34,479
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 2008, MVP
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

Post #458927
Posted Friday, February 22, 2008 4:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:44 AM
Points: 12, Visits: 157
Thank you, I will try, thanks a lot.
Post #459025
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse