Moving a SQL Database to a new location

[cmiddl1]

I have a problem that needs to be addressed ASAP, and would appreciate some assistance. I have a server that has SQL2000 installed on a drive that is running out of space. I have an additional drive with more then enough room. How can I get the databases moved to this other drive, still have SQL see the db, and not have to reinstall SQL. Is there a way that this can be done?

Thanks. "tek-tips rocks!!

 

[paul172]

It can all be done through Enterprise Manager! Just detach the DB using EM, move the MDF and LDF files to the new disk, then re-attach the DB's(again using EM), telling SQL where the new DB's are.

Can all be done in a matter of minutes, depending on the size of DB to copy over to the new disk.

 

[JayKusch]

Remember though ... BACKUP THE DATABASES FIRST!!!

Thanks

J. Kusch

 

[DebiJo]

Detach can be found if you right click the database in question and look under all tasks. No one can be in it, obviously. And make sure you note all the users that are setup and especially note any users that default to this database. As some security stuff may not come back when you re-attach. No big deal, just cleaner if you know everything up front.

Good luck,
Debi

 

[BrianBurgit]

When you say backup, do you mean just copy the mdf and ldf files to some other folder in case there's a problem?

 

[maswien]

Is it possible to not to detach the DB and also use another drive as the extra space?

 

[JayKusch]

BrainBurgit -

Nope ... I mean to run a backup job that will create a *.bak file of your DB. This can be accomplished by going into Enterprise Manager(EM) and drilling down to the desired DB. Then right click on the DB tab and choose All Tasks..Backup Database.

You can also use Query Analyzer and run the TSQL commands to back up your DB.

Another option would be to create a backup job thru a Maintenance Plan which is also located in EM.

mjia ...

yes you could detach the DB and maybe move the TLog/LDF to another drive to give you more room on the current drive if that is what you are asking.



Thanks

J. Kusch

 

[SQLSister]

And if you are not currently doing transaction log backups that could be why you are running out of space. The transaction log will grow to eat the whole hard drive if it is not backed up.




Questions about posting. See faq183-874