Moving Database Files Detach/Attach or ALTER DATABASE?

本文探讨了在SQL Server中使用Detach/Attach方法与ALTER DATABASE方法来移动数据库文件的不同之处。ALTER DATABASE方法更加现代且避免了一些潜在的问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

Moving Database Files Detach/Attach or ALTER DATABASE?

By Jonathan Kehayias, 2009/05/27

At times it can be necessary to move the data and or log files from one location to another on the same SQL Server. There are two ways to go about doing this task, detaching the database from the SQL Server Instance, moving the files to the new location in the operating system, and then reattaching the database to the SQL Server Instance, and using ALTER DATABASE with the MODIFY FILE option to move the files through a metadata switch, taking the database offline, moving the file in the operating system and then bringing the database back online. Both accomplish the same task, but there are a number of reasons why the ALTER DATABASE method can make more sense for doing this kind of task.

First lets look at the syntax of both operations. Using the AdventureWorks database as an example, to move the database files from their current location to a new one by detatching the database issue the following TSQL statement:

EXEC sp_detach_db N'AdventureWorks'

After the database is detached, the data files can be moved to their new location and the database can then be attached to the SQL instance with the following TSQL statement:

EXEC sp_attach_db N'AdventureWorks',
'c:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/AdventureWorks_Data.mdf',
'c:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/AdventureWorks_Log.LDF'

This probably isn't news to many people since this is how moving databases has been performed in SQL Server for a long time. In fact there are Microsoft Knowledgebase articles that cover through SQL Server 2005, showing this as an appropriate method to move database files.

However, there are a number of problems that can be introduced by using this legacy, and soon to be deprecated method in SQL Server 2005 and SQL Server 2008. The sp_attach_db command topic in the Books Online has the following common warning for features that will be removed in the future:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).

To move the data files for AdventureWorks Doing a detach/attach operation on a simple database like AdventureWorks using the more current ALTER DATABASE first you need to identify the filenames associated with the database:

select name, physical_name
from sys.master_files
where database_id = db_id('AdventureWorks')

Once the filename and physical_name have been determined, the database can be moved using ALTER DATABASE with the MODIFY FILE command as follows:

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME = 'D:/SQLData/AdventureWorks_Data.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'D:/SQLData/AdventureWorks_Log.ldf');

Once you have run the above statements, to complete the move, set the database offline:

ALTER DATABASE AdventureWorks SET OFFLINE

and then move the data files to the new location, then bring the database back online:

ALTER DATABASE database_name SET ONLINE

So why exactly is this important, and what difference does it really make? Well, there are a number of things that can be affected by the use of attach/detach that are not affected when using ALTER DATABASE. For example if your database uses Service Broker, by using detach/attach, Service Broker is disabled on the database, whereas when using ALTER DATABASE MODIFY FILE, Service Broker remains enabled. To re-enable Service Broker for the database requires exclusive access to the database, which means that you will have to kick any active connection out of the database to use ALTER DATABASE ENABLE BROKER, once you realize that there is a problem. In addition, if you have enable TRUSTWORTHY for the database for SQLCLR or cross database ownership chaining, this is disabled using attach/detach where it is not using ALTER DATABASE MODIFY FILE. The reason for this is security. When you attach a database, it may not be from a trusted source, and for this reason, TRUSTWORTHY is always disabled upon attaching the database making it necessary for a DBA to reset this flag marking the database as trusted.

While it is possible to still move a database to a different file system location using detach/attach, there are potential unplanned consequences to doing so. For expedience and stability of your application/database, ALTER DATABASE should be the preferred method of moving the database inside of the same SQL Instance.

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值