April 16, 2008 at 1:19 am
Hi,
Is there a way of changing the primary data file in a DB to another one?
I need to remove the first file I created (I.e. the one that became the primary file) but SQL Server doesn't allow me to delete it even though it contains no data.
Regards,
Hanslindgren
April 17, 2008 at 10:42 am
Why have you to remove it?
April 21, 2008 at 1:23 am
Because of company policy we have to empty this drive and this includes removing (or moving away) any DB files on it. I have three primary files left on it...
April 21, 2008 at 11:55 am
Hans Lindgren (4/21/2008)
Because of company policy we have to empty this drive and this includes removing (or moving away) any DB files on it. I have three primary files left on it...
Hi,
Try transfering your databse to the new drive and then simply empty the current databse drive.
Thanks:)
Regards,
[font="Verdana"]Sqlfrenzy[/font]
April 21, 2008 at 12:20 pm
if all you want to do is move the files to another drive, use detach\attach or restore database with the 'with move ' clause.
thats presuming these are user databases and not system databases?
---------------------------------------------------------------------
April 22, 2008 at 9:28 am
Yes, they are user databases.
I know about the attach/detach process but I wanted to know if anyone knows how to change the primary file. The DBs are all online 24/7 and I wish to not having to schedule some downtime for this...
Hanslindgren
April 22, 2008 at 9:43 am
HAns,
I can think of no way to reallocate which is the primary filegroup.
possibly....
BACKUP DATABASE
add another file to the primary filegroup on your other drive
use dbcc shrinkfile(current primaryfile,emptyfile)
alter database remove file current primaryfile
BUT, this will leave you without a .mdf (unless you call new file .mdf)and should ideally be done when you have exclusive use.
Also never actually tried this on the primary .mdf file, so I recommend testing it on a test database first.
And anyway you still need to have downtime (or at least impinge on performance) so I would still go for the detach\attach after a backup.
---------------------------------------------------------------------
April 28, 2008 at 8:00 am
george sibbald (4/22/2008)
HAns,I can think of no way to reallocate which is the primary filegroup.
possibly....
BACKUP DATABASE
add another file to the primary filegroup on your other drive
use dbcc shrinkfile(current primaryfile,emptyfile)
alter database remove file current primaryfile
BUT, this will leave you without a .mdf (unless you call new file .mdf)and should ideally be done when you have exclusive use.
Also never actually tried this on the primary .mdf file, so I recommend testing it on a test database first.
And anyway you still need to have downtime (or at least impinge on performance) so I would still go for the detach\attach after a backup.
Thanks, but SQL Server does NOT ALLOW you to remove a Primary Datafile (independently if I have named it .SundayLunch instead of .MDF) which testing would have shown or what I hoped I wrote in my post with:
SQL Server doesn't allow me to delete it even though it contains no data.
(It's true that I should have written allow the deletion of a Primary Datafile instead of referring to what SQL Server not allows me to do... Sorry.)
Thank you anyway,
Hanslindgren
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply