Sql-server – How to change the location of the data files in SQL Server 2000

sql serversql-server-2000

I have an old SQL Server 2000 machine in a clustered environment. The data files are on Disk S: which is running out of space. I have another Disk B: in the same disk array which has lot of space. I want SQL Server to use the Disk B: instead of Disk S:

How do I setup SQL Server to store the data files on Disk B?

Any help would be greatly appreciated.

Thanks.

Best Answer

If you can plan some downtime, I would unattach the database from the server, move the physical files and then reattach the database.

  1. Right click on the database --> all tasks --> detach database.
  2. Move files
  3. Right click on 'Databases' --> all tasks --> attach database.

The other option you have is to add another data file on the b: drive (right click on database --> properties -- > data files). Once you do that, you can move some of your tables over there. Easiest way to do that is to do 'select into' on the second drive, then do a rename of the first and second table.

Related Question