August 18, 2002 at 7:34 am
I have a directive from management to standardize the location and naming convention of data files and log files. I want to change both the logical and physical names.
I also want to seperate the log files from data files. Log files on their own Raid1, keeping the data files on Raid1+0.
1) Are there stored procedures to do this?
2) Should/Can it be done through full backup/restore?
3) Do you have a recommendation on keeping system dbs on their own raid(1)/(1+0)? Or just keep their log/data files seperated in the same manner?
We are not using clustering and run SQL Server 2000 Enterprise.
Thanks,
Joseph
August 18, 2002 at 10:10 am
The best thing to do would be to deatach the files, move them to their new location, then attach them. Look the the stored procedures sp_attach_db and sp_detach_db in SQL Server Books Online.
As for keeping the system DB's on their own raid - I've never done. I would think that would only help if you had a lot of activity in those databases.
As for renaming the logical name, I'm not sure on how to do that.
Do a full backup of your database before you make any changes!
Diane
August 18, 2002 at 2:33 pm
It's not a bad idea to put the log files on a separate RAID set for two reasons, one is that log files tend to be sequential access and so benefit from being kept separate, the other is that it's greater redundancy - if you'll lose the data RAID set you still have the logs. In practice it's not always so easy, you may get better performance by just putting all on one drive set if the logs would have been on a smaller set, RAID 1 perhaps.
Detach/attach is not a bad way to go, you'll have to do extra work for any db that is replicated since you can't detach without dropping replication. I guess I can see the logic behind standardizing location, not sure I'd care too much about the file names. Nice part about this method is it's quick and easy, not nearly the time it would take to restore with move.
Andy
August 18, 2002 at 7:59 pm
Thank you for your help.
I did finally find out that the logical and physical files can be done with an alter database command.
I will be going with Raid1 on the log files and 4-disk raid1+0 for the data files.
Joseph
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply