August 15, 2008 at 1:51 am
Hi Experts,
I have a database having 1 mdf file,1 ndf file and 2 log files.The problem is that when i took a backup and when i tried to restore that ,the backup showing four files.How can i make it to one .mdf and one .ldf file???
Thanks in Advance
August 15, 2008 at 2:14 am
Hi,
I am not entired sure you can consolidate data files, as I have neverr been required to do so.
My best guess, hoping the database isnt too big, would be to create a new database with one mdf and one ldf, as well as the same schema and then use a DTS or SSIS package to move the data across from table to the next?
The only issue I have with this would be to satisfy your foreign key constraints etc?
I am sure the additional log file can just be dropped once it has been backed up, and then set the original log file size to the sum of the two files that were holding the log data. This is because Log files use sequence numbers, and from what I understand, works like a pipeline. But the data file is a little more complicated.
I'm interested to see if there are any othe suggestions from the SSC guys.
Regards,
Justin
August 15, 2008 at 5:43 am
am i correct in saying you want to remove the extra filegroup and extra log file?
to remove the filegroup all objects in that filegroup would need to be moved to the primary filegroup then you can remove it after that. As for the log i would backup the database, truncate the logs and remove the unneeded file, then follow with another full backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2008 at 6:24 am
Ratheesh.K.Nair (8/15/2008)
Hi Experts,I have a database having 1 mdf file,1 ndf file and 2 log files.The problem is that when i took a backup and when i tried to restore that ,the backup showing four files.How can i make it to one .mdf and one .ldf file???
A backup will restore a database in exactly the same condition it was at the time it was backed up. That includes number and arrangement of files. You will have to restore the database with the four files, then look at emptying the unwanted 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply