Hi, Im using SqlServer 2000 and I want to know if it's possible to change the location of (.mdf & .ldf) files? For eg: If it's located in D:MySqlDataTest.mdf D:MySqlLogTest.ldf I want it to be moved to E:MySqlDataTest.mdf E:MySqlLogTest.ldf If possible, how(syntax/reference links) & can it be done without detaching the database?
See if this helps: http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546 http://support.microsoft.com/kb/221465/ http://support.microsoft.com/kb/224071/
If you want the existing database to remain open to users and without detaching the database, I suggest RESTORE COPY script , then remove the old database and rename the new one with old name. example below Step 1 Backup existing database Backup database dev to disk ='E:sql2000 estoresdev.bak' Step 2 Check for logical name paths by running the command below on backedup database RESTORE FILELISTONLY FROM DISK = 'E:sql2000 estoresdev.bak' Step 3 Use old logical path name as listed in result of above command , the RESTORE will create a new database called dev2 from backup , to new location in F drive as mentioned below. RESTORE DATABASE dev2 FROM DISK ='E:sql2000 estoresdev.bak' WITH MOVE 'dev_data' TO 'F:userdatadev2.mdf', MOVE 'fin62_dev_log' TO 'F:userdatadev2.ldf' Note : make sure you have enough disk space for above operations and also take the backup at offpeak hours if your database is very large. Cheers Sat