March 10, 2008 at 11:20 pm
How do I consolidate multiple data files into 1 files. The data files have data. Is there a way to backup and restore the data as 1 data file
March 10, 2008 at 11:39 pm
In my Experience i created a database with 1 data file then used DTS to transfer the data from thos multiple data files to the database that i created with 1 data file
"-=Still Learning=-"
Lester Policarpio
March 11, 2008 at 1:36 am
For the non catalog files you can use:
DBCC SHRINKFILE ( file_name , EMPTYFILE )
and later on
alter database xyz REMOVE FILE logical_file_name
Read BOL for more info !
If you have multiple filegroups for your tables, you should
first move the objects back to the appropriate/desired filegroup.
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
March 11, 2008 at 11:32 am
Both Lester and ALZDBA methods works good. I had tried and tested both methods sometime back but I found ALZDBA method less painful if you have only two or three data files compared to creating a new database with one file and then migrating data from first database to another.
SQL DBA.
March 11, 2008 at 6:43 pm
I assume you have it on another filegroup other than the primary.
This is quite a pain, but you can create a new filegroup, add one data file to it. Then change the filegroup of all your tables you want to move to point to the new filegroup (in enterprise manager, it will recreate the table to your new filegroup). You have to recreate your indexes on the new filegroup. Then do the rest as ALZDBA instructed to empty the datafile and removing the filegroup.
_____________
Donn Policarpio
March 11, 2008 at 6:54 pm
$sanjayattray (3/11/2008)
Both Lester and ALZDBA methods works good. I had tried and tested both methods sometime back but I found ALZDBA method less painful if you have only two or three data files compared to creating a new database with one file and then migrating data from first database to another.
Agree ALZDBA's method is less painful
Maybe i'll have to try that approach...
"Still LEarning" 😀
"-=Still Learning=-"
Lester Policarpio
March 11, 2008 at 6:55 pm
donpolix (3/11/2008)
I assume you have it on another filegroup other than the primary.This is quite a pain, but you can create a new filegroup, add one data file to it. Then change the filegroup of all your tables you want to move to point to the new filegroup (in enterprise manager, it will recreate the table to your new filegroup). You have to recreate your indexes on the new filegroup. Then do the rest as ALZDBA instructed to empty the datafile and removing the filegroup.
Gandang Umaga PO!!!!
--Translation : Good Morning!!!
"-=Still Learning=-"
Lester Policarpio
March 11, 2008 at 7:00 pm
ALZDBA (3/11/2008)
For the non catalog files you can use:DBCC SHRINKFILE ( file_name , EMPTYFILE )
and later on
alter database xyz REMOVE FILE logical_file_name
Read BOL for more info !
If you have multiple filegroups for your tables, you should
first move the objects back to the appropriate/desired filegroup.
Does this means that the data in the data file will be transfered to another data file?? if i have 3 and i empty the 3rd one where will the data go?? 1st or 2nd??
"-=Still Learning=-"
Lester Policarpio
March 12, 2008 at 3:12 am
Suppose your filegroup contains 3 files,
data is balanced over these 3 files.
If you empty one file, the data will be spread over the remaining
2 files within the same filegroup.
Keep in mind you cannot remove the first file of the primary filegroup.
In wich file of both remaining files the data will reside, should not be
of any concern because it is supposed to be balanced.
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
March 12, 2008 at 4:40 am
Thank You very much 🙂
"-=Still Learning=-"
Lester Policarpio
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply