To move a data or log file as part of a planned relocation, follow these steps:

1. Run the following statement.
   
   ALTER DATABASE database_name SET OFFLINE

2. Move the file or files to the new location.
   
3. For each file moved, run the following statement.
   
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' )

4. Run the following statement.
   
   ALTER DATABASE database_name SET ONLINE

5. Verify the file change by running the following query.
   
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');

To relocate a file as part of a scheduled disk maintenance process, follow these steps:

1. For each file to be moved, run the following statement.
   
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

2. Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Stopping Services.
   
3. Move the file or files to the new location.
   
4. Restart the instance of SQL Server or the server. For more information, see Starting and Restarting Services.
   
5. Verify the file change by running the following query.
   
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');

If a file must be moved because of a hardware failure, use the following steps to relocate the file to a new location.

Important:
If the database cannot be started, that is it is in suspect mode or in an unrecovered state, only members of the sysadmin fixed role can move the file.

1. Stop the instance of SQL Server if it is started.
   
2. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt.
   
   • For the default (MSSQLSERVER) instance, run the following command.
     
     NET START MSSQLSERVER /f /T3608

   • For a named instance, run the following command.
     
     NET START MSSQL$instancename /f /T3608

   For more information, see How to: Start an Instance of SQL Server (net Commands).
   
3. For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.
   
   ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

   For more information about how to use the sqlcmd utility, see Using the sqlcmd Utility.
   
4. Exit the sqlcmd utility or SQL Server Management Studio.
   
5. Stop the instance of SQL Server.
   
6. Move the file or files to the new location.
   
7. Start the instance of SQL Server. For example, run: NET START MSSQLSERVER.
   
8. Verify the file change by running the following query.
   
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID(N'<database_name>');

To move a full-text catalog, use the following steps. Note that when you specify the new catalog location, only new_path is specified instead of new_path/os_file_name.

1. Run the following statement.
   
   ALTER DATABASE database_name SET OFFLINE

2. Move the full-text catalog to the new location.
   
3. Run the following statement where logical_name is the value in the name column in sys.database_files and new_path is the new location of the catalog.
   
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path')

4. Run the following statement.
   
   ALTER DATABASE database_name SET ONLINE

Alternatively, you can use the FOR ATTACH clause of the CREATE DATABASE statement to move a full-text catalog. The following example creates a full-text catalog in the AdventureWorks database. To move the full-text catalog to a new location, the AdventureWorks database is detached and the full-text catalog is physically moved to the new location. Then the database is attached specifying the new location of the full-text catalog.

USE AdventureWorks;
CREATE FULLTEXT CATALOG AdvWksFtCat AS DEFAULT;
GO
USE master;
GO
--Detach the AdventureWorks database.
sp_detach_db AdventureWorks;
GO
--Physically move the full-text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON 
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf'), 
    (FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf'),
    (FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO

The following example moves the AdventureWorks log file to a new location as part of a planned relocation.

USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks')
    AND type_desc = N'LOG';
GO
ALTER DATABASE AdventureWorks SET OFFLINE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.
ALTER DATABASE AdventureWorks 
    MODIFY FILE ( NAME = AdventureWorks_Log, 
                  FILENAME = 'C:\NewLoc\AdventureWorks_Log.ldf');
GO
ALTER DATABASE AdventureWorks SET ONLINE;
GO
--Verify the new location.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks')
    AND type_desc = N'LOG';

Concepts

Other Resources

Help and Information