What is SPID in SQL Server

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