February 21, 2008 at 7:31 am
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
February 21, 2008 at 11:07 am
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
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 21, 2008 at 11:13 am
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
February 21, 2008 at 11:24 am
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.
February 21, 2008 at 1:11 pm
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
February 21, 2008 at 3:02 pm
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.
February 21, 2008 at 10:56 pm
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
February 22, 2008 at 4:37 am
Thank you, I will try, thanks a lot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply