Use ALTER DATABASE to Move Databases

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

Quellen:

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s