June 21, 2002 at 9:10 am
Hi everyone,
I am running out of disk space on one of my drives. Is it possible to move the dB files to another hard drive without have to recreate the dB? I just really want to know what's the easiest and fastest way to accomplish this. Problem is replication is running.
Please help,
Christine
Aurora
June 21, 2002 at 9:16 am
Hi Christine,
Yes you can move DB's without recreating the DB itself. You can use the following commands...
EXEC sp_detatch_db 'pubs'
EXEC sp_attach_db 'pubs',
'e:\SQLData\pubs.mdf',
'l:\SQLLogs\pubs_log.ldf'
Obviously using your own drive paths.
If you do not need to re-attach the log file, you can use..
EXEC sp_attach_single_file_db 'pubs',
'e:\SQLData\pubs.mdf'
I have never used this command, but I presume it would re-create the log file for you in the same location as the data file?? Maybe someone could answer that for me??
Clive Strong
June 21, 2002 at 9:19 am
Sorry...I missed the replication bit on the end!! Doh! Never tried that one!
Sorry!
Clive Strong
June 21, 2002 at 9:45 am
Couple things:
1. You do not need to stop SQL to detach or attach. It is an online op.
2. I would just do the log files, be sure you adjust the paths correctly. If you do not include the log file, then I believe it goes into the default log file path. Someone would have to verify that.
3. On replication. Is this publisher, dist, or sub? If it's sub, then should be no issue. I would suspect with the others there would be no issue. It would be like the database was offline (or server reboot). When you attach, it should pick back up since all the internal database settings remain the same. Only the path is adjusted in the master tables.
Steve Jones
June 21, 2002 at 9:49 am
Thanks Steve and everyone else who assisted me. This was great!
One question though Steve, wouldn't I get a sharing violation error if I didn't stop SQl server first?
Christine
Aurora
June 21, 2002 at 10:59 am
No, detaching handles it through SQL so it all works fine. If the db is replicated you'll have to remove replication first. The alternative would be to add a new file for the db and/or log, then use dbcc emptyfile to transfer data from one file to the other. Disk intensive way to do it.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply