Saturday, September 13, 2008

Quickest way to change the location of a log file in SQL Server

The quickest way to move the log file to a different location is to Detach the database, Move the .ldf and then Attach the database

Step 1: Detach the Database:

Use this command to detach the database

sp_detach_db 'YourDBName'

Step 2: Move the log file (.ldf):

Move the log file to a different location. For eg: Move it from 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName_Log.ldf' to 'D:\Logback\YourDBName_Log.ldf'

Step 3: Attach the Database:

Once the file has been moved, attach the database again

sp_attach_db 'mydb','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName.mdf','D:\Logback\YourDBName_Log.ldf'

Note: To find out the current location of your database files, use the command 'sp_helpfile'

No comments: