Moving user databases to a new drive

Planned Relocation Procedure

First, let’s get the information we will need to write the T-SQL script to alter the databases. Use the script below to retrieve the database name, logical name of the file and the physical filename. We will need these when writing the script:

Now that we have this information, below is the steps needed to move (or rename) a database file (log or data). Replaces the various fields (database_name, logical_name and new_path\os_file_name) in the script segments below.

1. Make a backup of the database first. You should always do this before making ANY changes to a database.

2. Run the following statement to take the database offline. Adding “WITH ROLLBACK IMMEDIATE” makes it go much faster as all non-qualified transactions are rolled back:

3. Move (physically) the file or files to the new location (or drive).

4. Execute the following statement for each file moved. This will store the updated location in the system tables:

5. Execute the following statement to bring the database back online. If the path is wrong, you will receive an error and the database will remain offline:

6. Verify the file change by running the following query.

If steps 5 and 6 succeed, the database was successfully moved to the new location and is available for use.

Here is the full script:

Views – 1754

Leave a Reply