SQL: Moving log files for an existing database to another drive

So today, Chris asked me how to move log files or data files on SQL to another drive. My first response was to use the GUI and just detach and then move the files, reattach using the GUI. But he wanted to move just the log files, I don’t think it is very intuitive with the GUI, if even possible.

The KB article from Microsoft shows you how to do with SQL commands (sp_attach_db and sp_detach_db), which is really the correct way, although, it doesn’t say how to do it if you have multiple log files or data files.

Books online shows us:

sp_attach_db [ @dbname= ] ‘dbname’         , [ @filename1= ] ‘filename_n’ [ ,…16 ]

[ @filename1= ] ‘filename_n’ Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. Up to 16 file names can be specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file. The primary file contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.

After all that, you should be good to, moving your data and log files around all you want! And as a side note, if you have SQL 2000, use Query Analyzer to connect and run the commands, if you have SQL 2005, use SQL Server Management Studio (SSMS) or SQL Server Management Studio Express (SSMSE) to connect and run the commands.

Advertisements

One thought on “SQL: Moving log files for an existing database to another drive”

  1. To move just files use:

    ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_pathos_file_name’ )Then shut down instance, move files and restart instance.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s