Use ALTER DATABASE to Move Databases
浏览数：23 /
时间：2015年06月12日
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:
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:
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.
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
