修改数据库的名字

最新推荐文章于 2020-11-13 16:07:30 发布

转载最新推荐文章于 2020-11-13 16:07:30 发布 · 2.3k 阅读

数据库专栏收录该内容

20 篇文章

订阅专栏

本文详细介绍如何在 SQL Server 中更改数据库名称，包括修改数据库名、逻辑文件名和物理文件名等步骤。提供了完整的 T-SQL 脚本示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

  /* 
 
  该脚本示例如何完整的修改一个数据库的名称. 
 
  数据为原名称为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