修改数据库的名字

/*

该脚本示例如何完整的修改一个数据库的名称.

数据为原名称为DB_BEIJING，需要修改成DB_SHANGHAI

perfectaction 2012.12.19

*/

--判断是否存在同名的数据库，以防止误删除

USE master

GO

IF EXISTS ( SELECT name FROM sys.databases WHERE name = N 'DB_BEIJING' )

BEGIN

    RAISERROR( '请注意：数据库已存在！' ,15,1)

    RETURN

    --DROP DATABASE DB_BEIJING

END

GO

USE master

GO

--创建测试数库

CREATE DATABASE [DB_BEIJING] 

ON PRIMARY

( 

    NAME = N 'DB_BEIJING' , 

    FILENAME = N 'X:\DATA\DB_BEIJING.mdf' , 

    SIZE = 3072KB , 

    FILEGROWTH = 1024KB 

)

LOG ON

( 

    NAME = N 'DB_BEIJING_log' , 

    FILENAME = N 'W:\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(N 'DB_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 =N 'DB_BEIJING' , NEWNAME=N 'DB_SHANGHAI' )

GO

ALTER DATABASE DB_SHANGHAI MODIFY FILE ( NAME =N 'DB_BEIJING_log' , NEWNAME=N 'DB_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(N 'DB_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://bbs.csdn.net/topics/390322324