How to move Database/Transaction Log files

Discussion in 'Databases' started by shivakhanal, Jul 12, 2008.

0
  1. #1
    You can move Database & Transaction log files to a different location in 2 ways.
    a) sp_detach_db & then sp_attach_db

    » Make sure that no user is using the database.
    » Exec sp_detach_db northwind
    » Move the Data & log files to a different location
    » EXEC sp_attach_db 'Northwind', 'c:\mssql7\northwnd.mdf', 'c:\mssql7\northwnd.ldf'

    b) BACKUP and RESTORE using WITH MOVE

    » Backup :-
    Backup Database Northwind To Disk = 'C:\mssql7\backup\nwind.bak'
    Go

    » Restore :-
    USE Master
    Go

    RESTORE Database northwind from DISK = 'c:\mssql7\backup\nwind.bak'
    WITH MOVE 'Northwind' TO 'c:\mssql7\Northwnd.mdf',
    MOVE 'Northwind_log' TO 'c:\mssql7\Northwnd.ldf'
    Go

    c) Can be used only for moving Tempdb files.

    » Use ALTER Database statement to specify a different Path for the filename.

    ALTER DATABASE Tempdb MODIFY FILE (NAME = Tempdev, FILENAME = 'c:\mssql7\tempdb.mdf')

    ALTER DATABASE Tempdb MODIFY FILE (NAME = Templog, FILENAME = 'c:\mssql7\templog.ldf')


    » Restart SQL Server and delete the old files.
     
    shivakhanal, Jul 12, 2008 IP