Thursday, December 8, 2011

Tips: How to move databases using the ALTER DATABASE

By using the ALTER DATABASE statement, you can move any system or user-defined database files except for Resource database files. In order to move the files, it is required to specify the current logical name of the file and the new file path. Moreover, that should include the new file name. Make sure that you can move only the file at a time in this manner.
You can follow the following steps to move data or log files to a new location.
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

B   y
,