曲苑杂坛--修改数据库名和文件组名

/*
该脚本示例如何完整的修改一个数据库的名称.
数据库为原名称为DB_BEIJING,需要修改成DB_SHANGHAI
nzperfect 2012.12.19
*/

--判断是否存在同名的数据库,以防止误删除
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = NDB_BEIJING)
BEGIN
    RAISERROR(请注意:数据库已存在!,15,1)
    RETURN
    --DROP DATABASE DB_BEIJING
END
GO


USE master
GO
--创建测试数库
CREATE DATABASE [DB_BEIJING] 
ON PRIMARY 
( 
    NAME = NDB_BEIJING, 
    FILENAME = NX:\DATA\DB_BEIJING.mdf , 
    SIZE = 3072KB , 
    FILEGROWTH = 1024KB 
)
LOG ON 
( 
    NAME = NDB_BEIJING_log, 
    FILENAME = NW:\Log\DB_BEIJING_log.ldf , 
    SIZE = 1024KB , 
    FILEGROWTH = 1024KB 
)
GO

--以下为修改过程

--step 1 : 修改数据库名称
USE master
GO
ALTER DATABASE DB_BEIJING SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb DB_BEIJING,DB_SHANGHAI
GO
ALTER DATABASE DB_SHANGHAI SET MULTI_USER 
GO

--step 2 : 查看修改名称后的数据库逻辑名及物理文件名
USE master
GO
SELECT name AS [Logical Name], physical_name AS [DB File Path],type_desc AS [File Type],
state_desc AS [State] FROM sys.master_files
WHERE database_id = DB_ID(NDB_SHANGHAI)
GO
/*
Logical Name    DB File Path                File Type    State
DB_BEIJING        X:\DATA\DB_BEIJING.mdf        ROWS        ONLINE
DB_BEIJING_log    W:\Log\DB_BEIJING_log.ldf    LOG            ONLINE
*/

--step 3 : 修改数据库逻辑文件名称
USE master
GO
ALTER DATABASE DB_SHANGHAI SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME=NDB_BEIJING, NEWNAME=NDB_SHANGHAI)
GO
ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME=NDB_BEIJING_log, NEWNAME=NDB_SHANGHAI_log)
GO
ALTER DATABASE DB_SHANGHAI SET MULTI_USER 
GO

--step 4 : 修改数据库物理文件名称之前先打开xp_cmdshell支持
USE master
GO
sp_configure show advanced options,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure xp_cmdshell, 1
GO
RECONFIGURE WITH OVERRIDE
GO

--step 5 : 重命名数据库物理文件名称
USE [master]
GO
ALTER DATABASE DB_SHANGHAI SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
EXEC xp_cmdshell RENAME "X:\DATA\DB_BEIJING.mdf", "DB_SHANGHAI.mdf"
GO
EXEC xp_cmdshell RENAME "W:\Log\DB_BEIJING_log.ldf", "DB_SHANGHAI_log.ldf"
GO

--step 6 : 将数据库逻辑名称指向新的物理文件,并将数据库online
USE [master]
GO
ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME =DB_SHANGHAI, FILENAME = X:\DATA\DB_SHANGHAI.mdf)
GO
ALTER DATABASE DB_SHANGHAI MODIFY FILE (NAME =DB_SHANGHAI_log, FILENAME = W:\LOG\DB_SHANGHAI_log.ldf)
GO
ALTER DATABASE DB_SHANGHAI SET ONLINE

--step 7 : 查看全部修改完成后的数据库情况
USE master
GO
SELECT name AS [Logical Name], physical_name AS [DB File Path],type_desc AS [File Type],
state_desc AS [State] FROM sys.master_files
WHERE database_id = DB_ID(NDB_SHANGHAI)
GO
/*
Logical Name    DB File Path                File Type    State
DB_SHANGHAI        X:\DATA\DB_SHANGHAI.mdf        ROWS        ONLINE
DB_SHANGHAI_log    W:\LOG\DB_SHANGHAI_log.ldf    LOG            ONLINE
*/

--step 8 : 关闭xp_cmdshell支持
USE master
GO
sp_configure xp_cmdshell, 0
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure show advanced options,0
GO
RECONFIGURE WITH OVERRIDE
GO

-------------------------
--摘抄自:http://www.cnblogs.com/nzperfect/archive/2012/12/19/2825298.html

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。