Moving Data, Log, and Full-Text Files

If you have never had a reason to move a SQL Server data, log, or full-text catalog to a new location, then you cannot call yourself a DBA. During the lifetime of a database or the SQL Server instance, there are a number of scenarios that may require you to relocate the database. The usual reason to move a database is that the disk on which the database resides runs out of space and you need to move the files to a bigger volume. Moving is made possible by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement, as mentioned earlier. However, there are a few things to prepare before you make the move.

The simplest ways to move a database is are the backup/restore or detach/attach commands. These methods are also the only way to move database files to another instance of SQL Server or to another server. If you are moving the files to different disk locations on the same instance, you do not need to take the databases down. All it takes is the ALTER DATABASE statement.

Before you start, make sure you have the logical names of the database files you are going to move. You can get these names from the name column in the sys.master_files catalog view. Simply query the name column.

To move a data or log file, perform the following steps:

1. Execute the code ALTER DATABASE database_name SET OFFLINE. This will take the databases offline and allow you relocate them to their new home.

2. Once they are moved, you need to execute the following code: ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name'). This line rebinds the files to the instance.

3. Next run the code ALTER DATABASE database_name SET ONLINE. This brings the database back online for access.

4. You can then 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 on Failed Hardware

If you have to move a file because of a disk or server crash, you can attach the files if they are intact, or relocate the file to a new location if you can run T-SQL against the server as follows:

1. Start the instance of SQL Server in master-only recovery mode by running the following command at the command prompt: NET START MSSQLSERVER /f /T3608.

2. If you are working against a named instance, then execute the following code: NET START MSSQL$instancename /f /T3608.

3. Next run 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’ ). The sqlcmd utility is discussed in Chapter 4.

4. Next, stop the instance of SQL Server and move the file or files to the new location.

5. Finally, start SQL Server using the NET START MSSQLSERVER command.

You can run these scripts against the active node of a SQL Server cluster.