SQL Server Performance Forum – Threads Archive
How to merge datafiles to one file in the backup
Hi friends,Is there any way to make only one file instead of two datafiles in restoration of backup on another server?
I mean I have one backup dump with two datafiles and two logfiles with that dump. I am moving it to another server and now I want to make only one file for data and onefile for log on that server. Is there any way to do it in restoration process? Restore DATABASE database1
From DISK = ‘\serverE$database1.bak’
with
Move ‘Main_Data’ to ‘D:SQLDataMSSQLDatadatabase1.mdf’,
Move ‘Main_Log’ to ‘D:SQLDataMSSQLDatadatabase1_log.ldf’,
Move ‘database1_1_Data’ to ‘C:SQLDataMSSQLDatadatabase1S.mdf’,
Move ‘database1_1_Log’ to ‘C:SQLDataMSSQLDatadatabase1S_log.ldf’ Now I want only one datafile instead of Main_data and database1_1_data files
similarly for logfiles also. is there anyway to do that?
Thanks in advance.
~Claudia.
You could store the files into a file group and restore the whole file group.
ALTER DATABASE database1
REMOVE FILE …. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Generally there will be only one datafile for a database which is a primary data file with .MDF extension and all other secondary must be with .NDF extension. Try to do what LazyDBA suggested. 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.
Newbie here, so g’morning to you all. I’m bumping this, becasue I need to do exactly what Claudia wanted to do. Maybe I’m stupid, but the answers that were forthcoming haven’t actually helped me all that much. Could I ask someone to clarify please?
<br />Ok, if you have 2 or more data files.. <br /><br />Restore database on dev box OR on live database..<br /><br />1. Open Enterprises Manager–go to databases– select database and right click on it<br />2. go to all task– Shrink database<br />3. click on Shrink File button on left bottom<br />4. select file data file that you want to remove; from drop down box [top left]<br />5. from ‘Shrink Action’ box on same window.. choose ‘Empty the file’ Radio button<br />6. Click on ok [ this will take time depending upon data in file]<br /><br />Above steps will move all data from this .ndf file to another files and you have to repeat these steps if you have more then 2 data files.<br /><br />For Log files just take a tranaction log backup and issue alter database remove file command..OR delete from from Enterprises manager- right click on database-properties-Transaction log- select file-Delete.<br /><br />Let us know if you face any problem.. <br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br /><br /><br />Deepak Kumar<br /><br />–An eye for an eye and everyone shall be blind
Forgot to remind you that do not run above operation.. when you have maximum workload on your
server.. this operation may slow down database server and can cause blocking. this is recomanded to run during Night time OR weekends on production server OR if possible take downtime. Deepak Kumar –An eye for an eye and everyone shall be blind
]]>