SQL 2008 : Move Databae Files to Differnet Path

SQL 2008 : Move Databae Files to Differnet Path

The following commands can be used to move the log/data files for a SQL database Note this is an OFFLUINE process!

/* Find file locations for your database */
SELECT name, dbid FROM sysdatabases WHERE name = ‘lansweeperdb’
SELECT * FROM sys.master_files WHERE database_id  = 16

/* Offline the Database */
ALTER DATABASE lansweeperdb SET OFFLINE;

/* Now move files, use the file names gather in step 1*/
ALTER DATABASE lansweeperdb
MODIFY FILE ( NAME = lansweeperdb, FILENAME = ‘E:\SQLDB\lansweeperdb.mdf’ );


ALTER DATABASE lansweeperdb

MODIFY FILE ( NAME = lansweeperdb_log, FILENAME = ‘E:\SQLDB\lansweeperdb_log.ldf’ );

/* Now online the database */
ALTER DATABASE lansweeperdb SET ONLINE;