Search This Blog

Moving the Database files from One location to another

In Sql server 2005, you can do this easily using the "ALTER DATABASE .. MODIFY FILE" statement

For testing purpose, I am creating a Database Named "DBtest2" and changing the Database log file

   1: CREATE DATABASE DBTest2
   2: GO
   3: USE DBTest2 
   4:  
   5: SELECT name, physical_name AS CurrentLocation, state_desc
   6: FROM sys.master_files
   7: WHERE database_id = DB_ID(N'DBTest2');
   8: GO
   9:  

 

After the database, is created, if you wish, you can enter some data into the database, Otherwise you can proceed like this

Step 1 : Change the connection to Master



   1: USE Master 
   2: GO

 


Step2 :make database OFFLINE.



   1: ALTER DATABASE DBTest2 SET OFFLINE

 


Step3 : move the file Physically


This step is very crucial.You need to move that file physically from that folder to the new location.
Open the parent folder (Here 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA') ,
You can see both mdf and ldf files', make sure that you cut the appropriate file, in this case it is the Log file.
Cut that "DBTest2_log.LDF" file and paste it on "C:\"


Step4 : Update the system reference


Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command


Here I am going to change the Path from C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\DBTest2_log.LDF  to C:\DBTest2_log.LDF



   1: ALTER DATABASE DBTest2 MODIFY FILE ( NAME ='DBTest2_log', FILENAME = 'C:\DBTest2_log.LDF')

Step5 : Make database Online


And here comes the final step, if you have completed the above steps without any errors, then make the database Online. Now, if you accidentally moved a wrong file, you will get an error message.


 

ALTER DATABASE DBTest2 SET ONLINE
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DBTest2');
GO
USE DBTest2
GO

3 comments:

Omar said...

Many thanks to you for this valuable procedure, but can I run this procedure without make the database offline, like if I can make only the effected files offline ?

Sudhir DBAKings said...

Nice post very helpful

dbakings

AskMeSql said...

You need to make that particular database offline, otherwise you wont be able to move the files