Tip: Use ALTER DATABASE to Move Databases

Follow Our Daily Tips

• facebook.com/TechNetTips
• twitter.com/TechNetTips
• blogs.technet.com/tnmag
• TechNet Tips library

You can use the ALTER DATABASE statement to move any system or user-defined database files except for Resource database files.To move files, you specify the cur¬rent logical name of the file and the new file path, which includes the new file name.You can move only one file at a time in this manner.

To move data or log files to a new location, follow these steps:

1. Get the logical name of the data and log files associated with the database by typing the following:

USE master SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID("Personnel");

2. Take the database you want to work with offline by typing these commands:

ALTER DATABASE Personnel SET offline GO

3. Move one file at a time to the new location by typing the following:

ALTER DATABASE Personnel MODIFY FILE ( NAME = Personnel_Data, FILENAME = "C:\Data\Personnel_Data.mdf") GO

4. Repeat the previous step to move other data and log files.

5. Put the database online by typing the following commands:

ALTER DATABASE Personnel SET online GO

You can verify the change or changes by typing this:

USE master SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID("Personnel");

You can also move full-text catalogs by their logical name. But note that when you specify the new catalog location, you specify only new_path rather than new_path/file_name.To move a full-text catalog file to a new location, follow these steps:

1. Take the database you want to work with offline by typing the following:

ALTER DATABASE database_name SET offline GO

2. Move one file at a time to the new location by typing these commands:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = "new_path". GO

3. Repeat the previous step to move other full-text catalog files as necessary.

4. Put the database online by typing the following:

ALTER DATABASE database_name SET online GO

For more on using ALTER DATABASE, see the tip Change Settings with ALTER DATABASE in SQL Server 2008.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant, Second Edition by William R. Stanek.