Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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.

share|improve this answer
    
+1 for adding the actual commands to perform the detach and reattach process –  GrumpyMonkey May 19 '11 at 21:05

Just a thought,

Have you tried to detach the database copy the files to the new location on the b drive and then reattach it.

Leave the original files in place until your happy the database is working correctly then it should be save to remove them.

share|improve this answer

Where are you storing your transaction log files? If you are not already doing so, you can and should put those on separate physical drives. This will free up a lot of space and also speed up your ACID transactions.

share|improve this answer
    
Also, if depending on your recovery model....you could just truncate your log file and free up some space ;) –  user1875 May 20 '11 at 21:54

Of course, detach and attach is the best. But if your database is VERY big, detach, copy and attach may be very long operations. If you want to minimize database downtime and your database uses full recovery model, you may use the following scenario:

  • backup database (database is still working)
  • copy and restore the database with new name on new location
  • enter old database in single-user mode (now old database is not working)
  • make transaction log backup
  • copy and restore the log backup
  • detach old database
  • rename the database to old name.

but this scenario is useable when database is big, from 200GB+, and copy time is significant.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.