I have a database that is approx 1.1 terabytes.
The data is stored in 2 files in the Primary filegroup.
PRIMARY NapersoftArchive_Data H:\DataFileGroup4\NapersoftArchive.mdf 458092.00000
PRIMARY NapersoftArchive_Data_Second I:\DataFileGroup5\NapersoftArchive_Data_Second.ndf 857212.00000
I would like to move the NapersoftArchive_Data on H:\ to another drive and add another file to the Primary on
H:\ to pick up space for the current archive. Maybe PRIMARY NapersoftArchive_Data_Third.
Maybe I need a secondary filegroup, but this is a stop-gap measure for space reasons.
Now this may be a bad idea, but is there any way I can backup
the PRIMARY NapersoftArchive_Data H:\DataFileGroup4\NapersoftArchive.mdf
and restore only that file of the primary filegroup to a G:\ drive with a move command?
Todas las respuestas
You could try something like
1. Detach/Attach method.
2. Backup/Restore whole database with move command.
3. Make the database OFFLINE/Move file Physically/ALTER DATABASE ..MODIFY FILE/Make database ONLINE
Alter Database Modify File example -
ALTER DATABASE TestDB
MODIFY FILE (
,FILENAME = 'S:\MSSQL\Data\TestDB.LDF'
You can restore only one file, but the database will be unusable. Also, you can't change the layout (except for physical filename) of a database using backup/restore. For instance, you can't consolidate two files into one using backup/restore.
Why do you want to move the mdf file to a different drive and add another secondary file where the mdf file used to be? Why not just add that third file to the new location instead (and leave the mdf file where it is)?
In case you want to move a file for a database, type below in Books Online index, it has detailed instructions on how to move a database file:
Well...the whole problem is running out of space. I originally just wanted to create a new datafile on a drive we have space on G:\. However the application team thinks creating a new datafile on drive G:\ will cause write contention with the other files on G:\.
So they came up with an idea, to move the file that is iwritten to the least 'filea.mdf' to the G:\ drive to avoid contention and to create a new datafile where filea.mdf used to be drive h:\.
THe file is 477G, I was trying to move it the fastest possible way.
Hey there! This may be a bit off topic, but have a look at this:
That tool will help you manage your used space better. The treemap represents each file as a colored rectangle, the area of which is proportional to the file's size. You can see which clusters are more and less filled with data; all over your harddrive. Maybe it can help you figure out where you have extremely large data files.