SQL Server修改数据库名及物理数据文件名操作步骤
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、背景介绍</a></li><li><a href="#_label1">二、操作步骤</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">2.1 设置为单用户模式（断开连接）</a></li><li><a href="#_lab2_1_1">2.2 修改数据库名称</a></li><li><a href="#_lab2_1_2">2.3 查找逻辑文件名</a></li><li><a href="#_lab2_1_3">2.4 关闭数据库（detach）</a></li><li><a href="#_lab2_1_4">2.5 手动重命名物理文件（操作系统层）</a></li><li><a href="#_lab2_1_5">2.6 重新附加数据库（attach）并更改逻辑文件名</a></li><li><a href="#_lab2_1_6">2.7 修改逻辑文件名（可选但推荐）</a></li><li><a href="#_lab2_1_7">2.8 查找逻辑文件名</a></li><li><a href="#_lab2_1_8">2.9 设置为多用户模式</a></li></ul><li><a href="#_label2">总结 </a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、背景介绍</h2><p>当前有一 SQL Server 数据库，数据库名称为 study ，现需将该数据库名称修改为 studyold ，并同时根据新用户名修改物理数据文件的名称。</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、操作步骤</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>2.1 设置为单用户模式（断开连接）</h3>
<div class="jb51code"><pre class="brush:sql;">USE master;
ALTER DATABASE study SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
</pre></div>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2.2 修改数据库名称</h3>
<div class="jb51code"><pre class="brush:sql;">ALTER DATABASE study MODIFY NAME = studyold;
</pre></div>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>2.3 查找逻辑文件名</h3>
<div class="jb51code"><pre class="brush:sql;">USE sdcpold
SELECT name AS LogicalName, physical_name AS PhysicalName
FROM sys.master_files
WHERE database_id = DB_ID(N'studyold');
</pre></div>
<p>逻辑文件名和当前物理路径记录如下：</p>
<blockquote><p>逻辑文件名：study、study_log<br />物理路径：D:\Program Files\Microsoft SQL Server\MSSQL15.GPMQAS01\MSSQL\DATA\study.mdf、<br />D:\Program Files\Microsoft SQL Server\MSSQL15.GPMQAS01\MSSQL\DATA\study_log.ldf</p></blockquote>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.4 关闭数据库（detach）</h3>
<div class="jb51code"><pre class="brush:sql;">USE master;
EXEC sp_detach_db @dbname = N'studyold';
</pre></div>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2.5 手动重命名物理文件（操作系统层）</h3>
<p class="maodian"><a name="_lab2_1_5"></a></p><h3>2.6 重新附加数据库（attach）并更改逻辑文件名</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE DATABASE ON
(
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL15.GPMQAS01\MSSQL\DATA\studyold.mdf'
),
(
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL15.GPMQAS01\MSSQL\DATA\studyold_log.ldf'
)
FOR ATTACH;
</pre></div>
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>2.7 修改逻辑文件名（可选但推荐）</h3>
<div class="jb51code"><pre class="brush:sql;">ALTER DATABASE sdcpold MODIFY FILE (NAME = study, NEWNAME = studyold);
ALTER DATABASE sdcpold MODIFY FILE (NAME = study_log, NEWNAME = studyold_log);
</pre></div>
<p class="maodian"><a name="_lab2_1_7"></a></p><h3>2.8 查找逻辑文件名</h3>
<div class="jb51code"><pre class="brush:sql;">USE studyold
SELECT name AS LogicalName, physical_name AS PhysicalName
FROM sys.master_files
WHERE database_id = DB_ID(N'studyold');
</pre></div>
<p>逻辑文件名和当前物理路径记录如下：</p>
<blockquote><p>逻辑文件名：studyold、studyold_log<br />物理路径：D:\Program Files\Microsoft SQL Server\MSSQL15.GPMQAS01\MSSQL\DATA\studyold.mdf、<br />D:\Program Files\Microsoft SQL Server\MSSQL15.GPMQAS01\MSSQL\DATA\studyold_log.ldf</p></blockquote>
<p class="maodian"><a name="_lab2_1_8"></a></p><h3>2.9 设置为多用户模式</h3>
<div class="jb51code"><pre class="brush:sql;">ALTER DATABASE studyold SET MULTI_USER;</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>总结 </h2>
頁:
[1]