Link to home
Start Free TrialLog in
Avatar of Gordon Hughes
Gordon HughesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to combine two data files

I have a database which has 2 mdf data files, called MP2DATALIVE.mdf and MP2DATA2LIVE.mdf
I would like to only have 1 data file called MP2DATALIVE.mdf
How do I combine these data files
Avatar of julian_brunt
julian_brunt
Flag of United Kingdom of Great Britain and Northern Ireland image

Depends on the data within.
I would sugest you need to create some query to extract data from each and insert into the new file.  Again all very ddependant on the structure of data and queries within the files.
If that second file is of primay filegroup

To get logical names
select name, physical_name
from sys.database_file



DBCC SHRINKFILE (logicalname_file2, EMPTYFILE)
http://technet.microsoft.com/en-us/library/ms189493.aspx
ALTER DATABASE REMOVE ligicalfilename2
http://msdn.microsoft.com/en-us/library/bb522469.aspx
Avatar of Gordon Hughes

ASKER

Hi
Bothe files are shown as in the primary group
Not sure how I run the
select name, physical_name
from sys.database_file
which datbase do I run this against the database name is MP2Live
use MP2Live
select name, physical_name 
from sys.database_file

Open in new window

Hi

I did that and it comes up with did not recognise sys.database_file

Any ideas
Sorry, it's sys.database_files with an S  

http://msdn.microsoft.com/en-us/library/ms174397(v=sql.90).aspx
Hi jogos

OK got the names, see attachment
What do I do next?
Gordon
Logical-names.jpg
First run a full backup of your database (as before any big change)


DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
ALTER DATABASE REMOVE MP2DATALIVE2
Hi jogos
I have done a back up
The tried to run your statement against the MP2LIVE db, got the following:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MP2DATALIVE2'.

Any suggestions
Gordon
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi jogos
OK tried that and got this messaqge

Msg 2556, Level 16, State 1, Line 1
There is insufficient space in the filegroup to complete the emptyfile operation.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FILE'.

Any suggestions
Gordon
Can your other file grow?
Hi jogos

How do I check and change that
Gordon
Hi gogos

Thanks for you support and patience
The system is on a vitual drive so have extended to volume to get loads of space
Now trying your script again
Gordon
Hi jogos

Ok, increased drive space and found the MP2DATLIVE file was set to maximum value, so changed to unlimited
ran it and got this message

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FILE'.

I still have two data files, but don't know if thye system is using both

Any suggestions where I go from here
Gordon
Hi jogos

Should the script has some ()
to read like this DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
GO
ALTER DATABASE REMOVE FILE (MP2DATALIVE2)
go
Hi jogos

I have run a parse on both versions of the script and get the following

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FILE'.

Gordon
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok see you have found it yourself
OK
I will restore the database from the backup and then run it again
Should work OK
Will let you know
Gordon
Excellent Support