July 12, 2005 at 9:33 am
There is one SQL server DB backup which is having two data files. There is a
situation come accross to restore the database to one data files only.
Another question:
I have 3 data files in one production server. I want to remove one of them.
How to remove the same?
Could you please help me how to proceed on this?
Thans in advance!
Regards,
July 12, 2005 at 9:47 am
Which 2 files do you speak of?
an MDF, and a LDF? Sql server requires at least 2 files for a database.
If you detach the database and Delete the the LDF file Sql server will create a new one when you re-attach it.
You cannot delete the LDF if the database is attached.
If you have an additional db file on a database you can
ALTER DATABASE database
REMOVE FILE 'logical_file_name'
But the file must be empty first, there can be no database objects left on it, no tables or indexes. They all must be moved to another file in the database.
Use the DBCC SHRINKFILE with the EMPTYFILE option before using this.
July 12, 2005 at 11:58 am
Hi
Thanks for reply..
I will try this option and get back to you.
First question was that I have backup dump which having 2 MDF and 1 LDF files. I have to restore the backup with 1 MDF file.
I have only backup dump(.BAK) not the actual database running on SQL server. This is dump file (2 years old)
Regards,
July 13, 2005 at 9:42 am
I haven't tried it myself, but you might be able to do what you want using the RESTORE with MOVE option, then specify the same filename for both files of the old DB. You can find the names of the original files (which you will need for the restore operation) using the Restore Verify Only option.
My hovercraft is full of eels.
July 13, 2005 at 10:40 am
All the issues have been resolved..Thanks a lot
July 13, 2005 at 11:37 am
Jay,
What was your solution?
You should always post your solution or acknowledge the posted solution. This way another person who might have the same issue will know what solved it for you.
-SQLBill
July 13, 2005 at 1:31 pm
Hi
Sorry for not rid it
Question 1:-
I have backup dump which having 2 MDF and 1 LDF files. I have to restore the backup with 1 MDF file.
I have only backup dump(.BAK) not the actual database running on SQL server. This is dump file (2 years old)
Answer:- RESTORE with MOVE option, then specify the same filename for both files of the old DB. We can find the names of the original files.
Question 2:-
I have 3 data files in one production server. I want to remove one of them.
How to remove the same?
Answer:-
Use the DBCC SHRINKFILE with the EMPTYFILE option. This will move all the contents into different MDF files. command as follow:
DBCC SHRINKFILE (FILE ID, EMPTYFILE)
Then run this command to remove the file
ALTER DATABASE database
REMOVE FILE 'logical_file_name'
But the file must be empty first, there can be no database objects left on it, no tables or indexes. They all must be moved to another file in the database.
Regards,
July 15, 2005 at 5:29 am
hi Prasad,
How can you do it ? (your first answer)
Lets take a example:
I have a database filegrpdb1 with these 8 data and 1 log file.
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrppf1_dat.mdf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp1f1_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp2f1_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp2f2_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp3f1_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp3f2_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp3f3_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_fgrp4f1_dat.ndf
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1_filegrpdb_log.ldf
I also have a database backup of the database.
Now I want to have only 1 mdf and 1 ldf file from the backup.
So I use the following command :
restore filelistonly from disk ='c:\filegrp' (this is my backup file)
It gave me the logical file names.
I used this command to restore all the .ndf files to 1 mdf file :
restore database filegrpdb1_new
from disk= 'c:\filegrp'
with
move 'fgrppf1_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp1f1_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp2f1_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp2f2_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp3f1_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp3f2_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp3f3_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'fgrp4f1_dat' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf',
move 'filegrpdb_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb_log1.ldf'
I get the error :
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp1f1_dat'(3) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp2f1_dat'(4) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp2f2_dat'(5) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp3f1_dat'(6) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp3f2_dat'(7) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp3f3_dat'(8) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2KA\data\\filegrpdb1.mdf' is claimed by 'fgrp4f1_dat'(9) and 'fgrppf1_dat'(1). The WITH MOVE clause can be used to relocate one or more files.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Please explore .
Thanks.
Kishore
July 25, 2005 at 8:43 am
You are right, I am also getting the same error. I might have done some mistake..
Thansk for correct me.. I will have a look and get back to you..
Jay
July 29, 2005 at 4:37 am
Hi,
I have tried lot of options but still not found the solution..
anyone have any idea?
Jay
July 29, 2005 at 7:25 am
If this were my database, I'd probably DTS or bulk copy everything out to a new database with only one MDF file. Once you've made sure everything is in the new database you can just detach or drop the old one and rename the new one and you're in business. Just a thought.
My hovercraft is full of eels.
December 27, 2007 at 9:25 am
Change the output path so you are using two MDF files.
December 28, 2007 at 5:02 am
Kishore, you cannot use restore to move all the .ndf files into one .mdf output file. The restore needs to be on a '1 file in - 1 file out' basis. All the with move does is alter the physical location and/or name of the output file .
so you have to restore it moving each file where you want it, then use the dbcc shrinkfile(emptyfile) and alter datbase statements to get rid of .ndf files.
---------------------------------------------------------------------
September 15, 2009 at 7:01 am
I had the same problem and solve. Look at:
RESTORE DATABASE [MYDB200to2008] FROM
DISK = N'E:\MSSQL10.IALPHA05\MSSQL\Backup\MYDB200to2008.bak'
WITH
MOVE N'MYDB200to2008_Data' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.mdf',
MOVE N'MYDB200to2008_FG_MaioresTab_1' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF',
MOVE N'MYDB200to2008_FG_MaioresTab_2' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF'
, MOVE N'MYDB200to2008_FG_OFF_MaioresTab_1' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF'
, MOVE N'MYDB200to2008_FG_OFF_MaioresTab_2' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF',
MOVE N'MYDB200to2008_Indices_1' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF',
MOVE N'MYDB200to2008_Indices_2' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF',
MOVE N'MYDB200to2008_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.LDF',
MOVE N'MYDB200to2008_2_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.LDF',
MOVE N'MYDB200to2008_3_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.LDF',
MOVE N'MYDB200to2008_4_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.LDF'
, REPLACE
ERROR RESULT
Msg 3176, Level 16, State 1, Line 1
File 'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF' is claimed by 'MYDB200to2008_FG_MaioresTab_2'(7) and 'MYDB200to2008_FG_MaioresTab_1'(6). The WITH MOVE clause can be used to relocate one or more files.
Msg 3176, Level 16, State 1, Line 1
File 'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.NDF' is claimed by 'MYDB200to2008_FG_OFF_MaioresTab_1'(8) and 'MYDB200to2008_FG_MaioresTab_1'(6). The WITH MOVE clause can be used to relocate one or more files.
Msg 3176, Level 16, State 1, Line 1
SO, I CHANGED TO THIS AND RESOLVE MY PROBLEM:
RESTORE DATABASE [MYDB200to2008] FILE = N'MYDB200to2008_Data'
, FILE = N'MYDB200to2008_FG_MaioresTab_1'
, FILE = N'MYDB200to2008_FG_MaioresTab_2'
, FILE = N'MYDB200to2008_FG_OFF_MaioresTab_1'
, FILE = N'MYDB200to2008_FG_OFF_MaioresTab_2'
, FILE = N'MYDB200to2008_Indices_1'
, FILE = N'MYDB200to2008_Indices_2'
FROM DISK = N'E:\MSSQL10.IALPHA05\MSSQL\Backup\MYDB200to2008.bak' WITH FILE = 1
, MOVE N'MYDB200to2008_Data' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008.mdf'
, MOVE N'MYDB200to2008_FG_MaioresTab_1' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_0.NDF'
, MOVE N'MYDB200to2008_FG_MaioresTab_2' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_1.NDF'
, MOVE N'MYDB200to2008_FG_OFF_MaioresTab_1' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_2.NDF'
, MOVE N'MYDB200to2008_FG_OFF_MaioresTab_2' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_3.NDF'
, MOVE N'MYDB200to2008_Indices_1' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_4.NDF'
, MOVE N'MYDB200to2008_Indices_2' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_5.NDF'
, MOVE N'MYDB200to2008_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_6.LDF'
, MOVE N'MYDB200to2008_2_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_7.LDF'
, MOVE N'MYDB200to2008_3_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_8.LDF'
, MOVE N'MYDB200to2008_4_Log' TO N'E:\MSSQL10.IALPHA05\MSSQL\DATA\MYDB200to2008_9.LDF'
, NOUNLOAD, REPLACE, STATS = 10
GO
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply