---------------------------------------------------------------------------------- -- author : htl258(tony) -- date : 2010-06-26 21:51:30 -- version: microsoft sql server 2008 (rtm) - 10.0.1600.22 (intel x86) -- jul 9 2008 14:43:34 -- copy
----------------------------------------------------------------------------------
-- author : htl258(tony)
-- date : 2010-06-26 21:51:30
-- version: microsoft sql server 2008 (rtm) - 10.0.1600.22 (intel x86)
-- jul 9 2008 14:43:34
-- copyright (c) 1988-2008 microsoft corporation
-- developer edition on windows nt 5.1 (build 2600: service pack 2)
-- blog : (转载保留此信息)
-- subject: sql查询与修改数据库逻辑文件名，移动数据库存储路径示例
----------------------------------------------------------------------------------
use mydb
go
--1.查询当前数据库的逻辑文件名
select file_name(1) as 'file name 1', file_name(2) as 'file name 2';
--或通过以下语句查询：
--select name from sys.database_files
/*
file name 1 file name 2
------------------- --------------------
mydb mydb_log
(1 行受影响)
*/
--2.修改数据文件或日志文件的逻辑名称
alter database [mydb]
modify file ( name = mydb, newname = new_mydb )
alter database [mydb]
modify file ( name = mydb_log, newname = new_mydb_log )
--查询更改后的名称：
select file_name(1) as 'file name 1', file_name(2) as 'file name 2';
/*
file name 1 file name 2
------------------- --------------------
new_mydb new_mydb_log
(1 行受影响)
*/
--3.若要将数据文件或日志文件移至新位置,先offline把文件move到相应的位置，然后再online
-- 详细参考以下步骤
--3.1 查询数据文件或日志文件当前存放路径：
select physical_name from sys.database_files
/*
physical_name
-------------------------------------------------------------------------------------------
d:/program files/microsoft sql server/mssql10.mssqlserver/mssql/data/mydb.mdf
d:/program files/microsoft sql server/mssql10.mssqlserver/mssql/data/mydb_log.ldf
(2 行受影响)
*/
--3.2 移动路径
use [master]
go
--3.2.1 关闭所有进程
declare @str varchar(500);
set @str = '';
select @str = @str + 'kill '+rtrim(spid)+';'+char(13)+char(10)
from master.dbo.sysprocesses
where dbid=db_id('mydb');
exec(@str);
go
--3.2.2 设置数据库脱机
alter database [mydb] set offline
go
--3.2.3 移动数据文件与日志文件(为确保数据安全，建议改move为copy,复制成功后再删除源文件)
exec master..xp_cmdshell 'move "d:/program files/microsoft sql server/mssql10.mssqlserver/mssql/data/mydb.mdf" "e:/dbtest"',no_output
exec master..xp_cmdshell 'move "d:/program files/microsoft sql server/mssql10.mssqlserver/mssql/data/mydb_log.ldf" "e:/dbtest"',no_output
go
--3.2.4 设置新的存储路径
alter database [mydb] modify file (name = new_mydb,filename = 'e:/dbtest/mydb.mdf')
alter database [mydb] modify file (name = new_mydb_log,filename = 'e:/dbtest/mydb_log.ldf')
go
--3.2.5 设置数据库联机
alter database [mydb] set online
go
--3.2.6 查询新的路径
use mydb
go
select physical_name from sys.database_files
/*
physical_name
------------------------------
e:/dbtest/mydb.mdf
e:/dbtest/mydb_log.ldf
(2 行受影响)
*/
.syntaxhighlighter{padding-top:20px;padding-bottom:20px;}【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！