Home > Database > Mysql Tutorial > body text

sql server:alter database name problem

一个新手
Release: 2017-10-18 09:57:49
Original
2134 people have browsed it


--step 1 : 修改数据库名称
USE master
GO
ALTER DATABASE GeovinDuCms SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'GeovinDuCms','DuCms'
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'DuCms')
GO

--step 3 : 修改数据库逻辑文件名称
USE master
GO
ALTER DATABASE DuCms SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DuCms MODIFY FILE (NAME=N'GeovinDuCms', NEWNAME=N'DuCms')
GO
ALTER DATABASE DuCms MODIFY FILE (NAME=N'GeovinDuCms_log', NEWNAME=N'DuCms_log')
GO
ALTER DATABASE DuCms 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 DuCMS SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
EXEC xp_cmdshell 'RENAME "E:\2005database\GeovinDuCms.mdf", "DuCms.mdf"'
GO
EXEC xp_cmdshell 'RENAME "E:\2005database\GeovinDuCms_log.ldf", "DuCms_log.ldf"'
GO

--step 6 : 将数据库逻辑名称指向新的物理文件,并将数据库online
USE [master]
GO
ALTER DATABASE DuCMS MODIFY FILE (NAME =DuCms, FILENAME = 'E:\2005database\DuCms.mdf')
GO
ALTER DATABASE DuCMS MODIFY FILE (NAME =DuCms_log, FILENAME = 'E:\2005database\DuCms_log.ldf')
GO
ALTER DATABASE DuCMS 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'DuCMS')
GO

--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

USE DuCMS
GO
Copy after login

The above is the detailed content of sql server:alter database name problem. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template