Move a database to new hard drive

[GerryGoldberg]

I have SQL Server 2000 installed on the C:\ drive of my desktop that I use as a test platform for a number of applications that I maintain. This installation contains several databases, one of which has grown substantially and is threatening to take over my hard disk. Is it possible to move this database to another drive, specifically a network drive (F:\), without having to install an second version of SQL Server 2000 on the F:\ drive, and have the C:\ version of SQL server simply point to it. In other words, for a single installation of SQL server, can one or more of its databases reside on a hard drive different from that which contains the SQL server programs? If this is possible, how to I point SQL server to the new hard drive location?

Thanks,

Gerry Goldberg

 

[Dilyias]

First off - why would you want to have your db files on a network drive? You should keep it local or on a san.

Yeah you can point it to a different drive.

Easiest way for you is to use enterprise manager, right click on the database and do perform a full backup. Then right click on it and choose the restore option, pick the recent backup, and in the tabs you can specify a location for the new database files.

The old .mdf and .ldf files will probably exist in your C:\ drive after the restore so you'll want to go delete them after you've verified the new db is on F:\.

 

[royjimenez]

I think it's not a good idea, u should install a new hard disk in your server if u don't have enough space.

Regards

The life is too short to cry and long enough to try it... God bless us.

 

[SQLSister]

Basically you detach the database and copy to a new drive.
When you attach the database, you specify the new location.