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.
If you can plan some downtime, I would unattach the database from the server, move the physical files and then reattach the database.
- Right click on the database –> all tasks –> detach database.
- Move files
- 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.
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.
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.
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.
Regarding the location of the Transaction Log files, be sure to check with the Disk Administrator.
In today’s SAN/LUN disk management environment, it is quite likely that all the server’s drives are on the same physical array anyway, unless you made a specific request at server creation to put the Transaction Logs on a different LUN.