SQLServer如何完整的修改一个数据库的名称

数据库为原名称为DB_BEIJING，需要修改成DB_SHANGHAI
nzperfect
2012.12.19*/
--判断是否存在同名的数据库，以防止误删除USE
masterGOIF
EXISTS
(SELECT
name
FROM
sys.databases
WHERE
name
=
N'DB_BEIJING')BEGINRAISERROR('请注意：数据库已存在！',15,1)RETURN--DROP
DATABASE
DB_BEIJINGENDGOUSE
masterGO--创建测试数库
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
masterGOALTER
DATABASE
DB_BEIJING
SET
SINGLE_USER
WITH
ROLLBACK
IMMEDIATEGOEXEC
master..sp_renamedb
'DB_BEIJING','DB_SHANGHAI'GOALTER
DATABASE
DB_SHANGHAI
SET
MULTI_USERGO
--step
2
:
查看修改名称后的数据库逻辑名及物理文件名USE
masterGOSELECT
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
masterGOALTER
DATABASE
DB_SHANGHAI
SET
SINGLE_USER
WITH
ROLLBACK
IMMEDIATEGOALTER
DATABASE
DB_SHANGHAI
MODIFY
FILE
(NAME=N'DB_BEIJING',
NEWNAME=N'DB_SHANGHAI')GOALTER
DATABASE
DB_SHANGHAI
MODIFY
FILE
(NAME=N'DB_BEIJING_log',
NEWNAME=N'DB_SHANGHAI_log')GOALTER
DATABASE
DB_SHANGHAI
SET
MULTI_USERGO
--step
4
:
修改数据库物理文件名称之前先打开xp_cmdshell支持USE
masterGOsp_configure
'show
advanced
options',1GORECONFIGURE
WITH
OVERRIDEGOsp_configure
'xp_cmdshell',
1GORECONFIGURE
WITH
OVERRIDEGO
--step
5
:
重命名数据库物理文件名称
USE
[master]GOALTER
DATABASE
DB_SHANGHAI
SET
OFFLINE
WITH
ROLLBACK
IMMEDIATEGOEXEC
xp_cmdshell
'RENAME
X:\DATA\DB_BEIJING.mdf,
DB_SHANGHAI.mdf'GOEXEC
xp_cmdshell
'RENAME
W:\Log\DB_BEIJING_log.ldf,
DB_SHANGHAI_log.ldf'GO
--step
6
:
将数据库逻辑名称指向新的物理文件，并将数据库online
USE
[master]GOALTER
DATABASE
DB_SHANGHAI
MODIFY
FILE
(NAME
=DB_SHANGHAI,
FILENAME
=
'X:\DATA\DB_SHANGHAI.mdf')GOALTER
DATABASE
DB_SHANGHAI
MODIFY
FILE
(NAME
=DB_SHANGHAI_log,
FILENAME
=
'W:\LOG\DB_SHANGHAI_log.ldf')GOALTER
DATABASE
DB_SHANGHAI
SET
ONLINE
--step
7
:
查看全部修改完成后的数据库情况USE
masterGOSELECT
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