runal_jagtap (9/18/2012)
Thanks Perry 🙂Can you give an example, rough pathfilename etc for this syntax query?
ALTER DATABASE mydb MODIFY FILE(name=logicalname,
FILENAME='new path and drive\the originalfilename.extension')
Yes sure. Let's a ssume i have a database named BOB. The result of my first query above produced
name physical_name
Bob_data C:\Program Files\MSSQL.1\MSSQL\data\Bob.mdf
Bob_Log C:\Program Files\MSSQL.1\MSSQL\data\Bob_log.ldf
I want to move data files to "F:\MSSQL\Data" and logs to "G:\MSSQL\Logs", my statements would be
ALTER DATABASE [bob] MODIFY FILE(name=Bob_data,
FILENAME='F:\MSSQL\Data\Bob.mdf')
ALTER DATABASE [bob] MODIFY FILE(name=Bob_log,
FILENAME='G:\MSSQL\Logs\Bob_log.ldf')
Note: moving system databases such as TEMPDB is a different process to user databases
runal_jagtap (9/18/2012)
Also will this approach help in improving perfromance?????????????
This all depends on how your disk subsystem is made up. Do you have separate physical disks or just logical drives on the same disk set or SAN attached LUNs, etc?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉