How to combine two data files

Gordon Hughes
Gordon Hughes used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Commented:
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
Gordon HughesDirector

Author

Commented:
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
use MP2Live
select name, physical_name 
from sys.database_file

Open in new window

Gordon HughesDirector

Author

Commented:
Hi

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

Any ideas

Commented:
Sorry, it's sys.database_files with an S  

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

Author

Commented:
Hi jogos

OK got the names, see attachment
What do I do next?
Gordon
Logical-names.jpg

Commented:
First run a full backup of your database (as before any big change)


DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
ALTER DATABASE REMOVE MP2DATALIVE2
Gordon HughesDirector

Author

Commented:
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
Commented:
DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
GO
ALTER DATABASE REMOVE FILE MP2DATALIVE2
go
Gordon HughesDirector

Author

Commented:
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

Commented:
Can your other file grow?
Gordon HughesDirector

Author

Commented:
Hi jogos

How do I check and change that
Gordon
Gordon HughesDirector

Author

Commented:
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
Gordon HughesDirector

Author

Commented:
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
Gordon HughesDirector

Author

Commented:
Hi jogos

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

Author

Commented:
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
Gordon HughesDirector
Commented:
Hi jogos

will this work

DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
GO
ALTER DATABASE MP2LIVE REMOVE file MP2DATALIVE2
go

It accepts this with the parse
Gordon
Commented:
Name of database missing in command, if name is MP2LIVE
ALTER DATABASE MP2LIVE  REMOVE FILE  MP2DATALIVE2
go

Open in new window

Commented:
Ok see you have found it yourself
Gordon HughesDirector

Author

Commented:
OK
I will restore the database from the backup and then run it again
Should work OK
Will let you know
Gordon
Gordon HughesDirector

Author

Commented:
Excellent Support

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial