locked
Move log files for SQL 2005 databases to another drive RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

    Hello,

     

    I need to move all log files for my SQL 2005 databases to another drive.  I don't wish to shrink the files, I need to move the logs to another drive spindle.  I did find an article (Article ID: 224071) that describes moving both the database and logs using sp_detach and then sp_attach.  What is the best way just to move the logs to another drive on the same server, and that keeps the databases in their original location?

    Thanks.

    Thursday, January 24, 2008 5:15 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes you can move the log files to another drive using detach and attach procedures. Detach\Attach works fine only thing you need to note down is the default databases. Before you perform detach\attach operation just note down the list of all the logins and their default databases, because once you reattach it the default databases of the logins will get changed and hence there will be login failure in most cases.
    1. Note down default dbs for all your logins
    2. detach your dbs
    3. move it another location
    4. attach it
    5. compare the logins with step 1 and change it if the default dbs have changed.
    Refer the below link for moving system databases in Sql Server 2005,
    http://sql-articles.com/index.php?page=articles/msysdb.htm
    http://support.microsoft.com/kb/224071

    - Deepak
    Thursday, January 24, 2008 5:26 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes you can move the log files to another drive using detach and attach procedures. Detach\Attach works fine only thing you need to note down is the default databases. Before you perform detach\attach operation just note down the list of all the logins and their default databases, because once you reattach it the default databases of the logins will get changed and hence there will be login failure in most cases.
    1. Note down default dbs for all your logins
    2. detach your dbs
    3. move it another location
    4. attach it
    5. compare the logins with step 1 and change it if the default dbs have changed.
    Refer the below link for moving system databases in Sql Server 2005,
    http://sql-articles.com/index.php?page=articles/msysdb.htm
    http://support.microsoft.com/kb/224071

    - Deepak
    Thursday, January 24, 2008 5:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Follow below steps:

    http://support.microsoft.com/kb/224071

    Moving the tempdb database

    You can move tempdb files by using the ALTER DATABASE statement.
    1. Determine the logical file names for the tempdb database by using sp_helpfile as follows: 
      use tempdb
go
sp_helpfile
go
      The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
    2. Use the ALTER DATABASE statement, specifying the logical file name as follows: 
      use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
      You should receive the following messages that confirm the change:
      Message 1
      File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
      Message 2
      File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
    3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
    4. Stop and then restart SQL Server.

    http://sql-bi-dev.blogspot.com.au/2010/06/moving-database-files-from-one-drive-to.html

    http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

    Thanks Shiven:) If Answer is Helpful, Please Vote


    Monday, August 13, 2012 4:53 AM