Tag Archives: mssql

Moving MS SQL 2008 database location

You cannot change the installation location (so master etc databases), but client databases can be moved like so:

First take the databases offline and move the mdf and ldf files to the new location, then do the following commands:

ALTER DATABASE “db_name” SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE “db_name” SET OFFLINE;
ALTER DATABASE “db_name” MODIFY FILE
(
   Name = “db_name”,
   Filename = ‘Q:\sqldata\db_name.mdf’
);
ALTER DATABASE “db_name” MODIFY FILE
(
   Name = “db_name_log”,
   Filename = ‘Q:\sqldata\db_name_log.LDF’
);
ALTER DATABASE “db_name” SET ONLINE;
ALTER DATABASE “db_name” SET MULTI_USER;

 

Taken from: http://stackoverflow.com/questions/6584938/move-sql-server-2008-database-files-to-a-new-folder-location