sqlserver库名,sql名称

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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