sql server重命名_在Linux上SQL Server中重命名逻辑和物理文件名

最新推荐文章于 2024-07-12 04:11:52 发布

culuo4781

最新推荐文章于 2024-07-12 04:11:52 发布

阅读量1.3k

点赞数 2

文章标签：数据库 linux java python ubuntu

原文链接：https://www.sqlshack.com/renaming-logical-and-physical-file-names-in-sql-server-on-linux/

版权

sql server重命名

Each database in SQL Server contains at least two files i.e. Data file (*.mdf) and log file (*.ldf). These database files have a logical name and the physical file name. Below we can view the simple architecture of a database in SQL Server.

SQL Server中的每个数据库至少包含两个文件，即数据文件（* .mdf）和日志文件（* .ldf）。这些数据库文件具有逻辑名和物理文件名。下面我们可以查看SQL Server中数据库的简单体系结构。

Whenever we rename a database, it does not change the logical and physical file name of the database. Ideally, we should associate the database name with the database file names because it creates confusion if the database name does not match the logical and physical file names. Therefore, we might have a requirement to rename the logical and the physical file names in the SQL Server instance. In this article, we will view the different methods of modifying the logical and physical file name in SQL Server with both GUI and the t-SQL.

每当我们重命名数据库时，它都不会更改数据库的逻辑和物理文件名。理想情况下，我们应该将数据库名称与数据库文件名称相关联，因为如果数据库名称与逻辑和物理文件名称不匹配，则会造成混淆。因此，我们可能需要重命名SQL Server实例中的逻辑和物理文件名。在本文中，我们将介绍使用GUI和t-SQL修改SQL Server中逻辑和物理文件名的不同方法。

In this article, I am using the SQL Server 2019 on Ubuntu.

在本文中，我在Ubuntu上使用SQL Server 2019。

First, let us create a sample database

首先，让我们创建一个示例数据库

CREATE DATABASE [SQLShack]
  ON PRIMARY 
( NAME = N'SQLShack', FILENAME = N'/var/opt/mssql/data/SQLShack.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'SQLShack_log', FILENAME = N'/var/opt/mssql/data/SQLShack_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 
GO

We can view the logical and physical file name using the below query

我们可以使用以下查询查看逻辑和物理文件名

select name as [Logical_name],
physical_name as [physical file name]
from sys.database_files

更改SQL Server数据库的逻辑文件 (Change the logical file for the SQL Server Database)

Suppose we want to change the logical filename for the newly created database. We want to rename the logical file name as SQLShack_Demo and SQLShack_log_Demo.

假设我们要更改新创建的数据库的逻辑文件名。我们想将逻辑文件名重命名为SQLShack_Demo和SQLShack_log_Demo。

To change the logical file name, view the database properties by right click on the database -> properties.

要更改逻辑文件名，请通过右键单击数据库->属性来查看数据库属性。

In the files page, we can view all the database files and their properties like file group, size, auto growth etc.

在文件页面中，我们可以查看所有数据库文件及其属性，例如文件组，大小，自动增长等。

In the logical name column, click on each logical file name and modify the desired name as shown here. Note: The name should not contain any special characters.

在逻辑名称列中，单击每个逻辑文件名，然后修改所需的名称，如下所示。注意：名称中不得包含任何特殊字符。

We can click on ‘Ok’ to make this change but let us generate the script of this operation. Click on ‘Script’ to generate a script of this logical file name change activity.

我们可以单击“确定”进行更改，但让我们生成此操作的脚本。单击“脚本”以生成此逻辑文件名更改活动的脚本。

Execute the generated script. In the below script, we can see the ‘NEWNAME’ for the logical file name in the alter database command.

执行生成的脚本。在下面的脚本中，我们可以在alter database命令中看到逻辑文件名的“ NEWNAME”。

USE [SQLShack]
GO
ALTER DATABASE [SQLShack] MODIFY FILE (NAME=N'SQLShack', NEWNAME=N'SQLShack_Demo')
GO
USE [SQLShack]
GO
ALTER DATABASE [SQLShack] MODIFY FILE (NAME=N'SQLShack_log', NEWNAME=N'SQLShack_log_Demo')
GO

In the output message, we get the confirmation that the new file has been set. Therefore, let’s view the logical filename using the script we executed before. It is showing the new logical name for the database.

在输出消息中，我们确认已设置新文件。因此，让我们使用之前执行的脚本查看逻辑文件名。它显示了数据库的新逻辑名称。

We can view the modified logical name in the SSMS database properties as well.

我们也可以在SSMS数据库属性中查看修改后的逻辑名称。

We have changed the logical file name for the database so far. In the next section, we will change the physical file name.

到目前为止，我们已经更改了数据库的逻辑文件名。在下一节中，我们将更改物理文件名。

更改SQL Server数据库的物理文件 (Change the physical file for the SQL Server Database)

We might need to change the physical file name as well for the database. Let’s view the steps to change the physical file as below.

我们可能还需要更改数据库的物理文件名。让我们查看更改物理文件的步骤，如下所示。

SQLShack.mdf to SQLShack_Demo.mdf
SQLShack.mdf到SQLShack_Demo.mdf
SQLShack_log.ldf to SQLShack_Demo_log.ldf
SQLShack_log.ldf到SQLShack_Demo_log.ldf

Open the terminal and go to the directory ‘var/opt/mssql/data’. Use the command below to go to the path.

打开终端，然后转到目录“ var / opt / mssql / data”。使用以下命令转到路径。

cd /var/opt/mssql/data

cd / var / opt / mssql / data

View the content of the directory using the ls-lrt command. You can view the highlighted database files.

使用ls-lrt命令查看目录的内容。您可以查看突出显示的数据库文件。

In Linux, we can use the filename using the ‘mv’ command. We use ‘mv’ command to move and rename a file in Linux from one directory to another.

在Linux中，我们可以使用'mv'命令使用文件名。我们使用“ mv”命令将Linux中的文件从一个目录移动并重命名到另一个目录。

Let us rename the file ‘SQLShack.mdf’ to ‘SQLShack_Demo.mdf’.

让我们将文件“ SQLShack.mdf”重命名为“ SQLShack_Demo.mdf”。

Using the ‘ls-lrt’ command, we can see that file name is modified to the new name. In a Windows system, we cannot modify the physical file name until the database is in the online status. In Linux, it allows us to change the physical file name.

使用“ ls-lrt”命令，我们可以看到文件名已修改为新名称。在Windows系统中，除非数据库处于联机状态，否则我们无法修改物理文件名。在Linux中，它允许我们更改物理文件名。

Similarly, rename the ‘SQLShack_log.ldf’ file to ‘SQLShack_demo.ldf’.

同样，将“ SQLShack_log.ldf”文件重命名为“ SQLShack_demo.ldf”。

We have changed the data files and the log physical file name in Linux. Let us view the database properties to see if it is reflecting in the database.

我们在Linux中更改了数据文件和日志物理文件名。让我们查看数据库属性以查看其是否反映在数据库中。

Here you can see, physical file names are changed at the operating system level but not at the database end. We need to modify the system catalog so that database can point to new physical filenames.

在这里您可以看到，物理文件名是在操作系统级别更改的，而不是在数据库端更改的。我们需要修改系统目录，以便数据库可以指向新的物理文件名。

Specify the new physical filename in the alter database command as shown below. Execute the below command with the new file name. We need to execute one statement per database file. If the database has multiple files we need to create alter database statements accordingly.

如下所示，在alter database命令中指定新的物理文件名。使用新文件名执行以下命令。我们需要为每个数据库文件执行一条语句。如果数据库有多个文件，我们需要相应地创建alter database语句。

ALTER DATABASE SQLShack MODIFY FILE (Name='SQLShack_Demo', FILENAME='/var/opt/mssql/data/SQLShack_Demo.mdf')
GO
ALTER DATABASE SQLShack MODIFY FILE (Name='SQLShack_log_Demo', FILENAME='/var/opt/mssql/data/SQLShack_Demo_log.ldf')
GO

In the next step, we will take the database offline and bring it back online. To take the database offline, we need to kill all database connections first and then take the database offline with the below command.

在下一步中，我们将使数据库脱机并使其重新联机。要使数据库脱机，我们需要先终止所有数据库连接，然后使用以下命令使数据库脱机。

USE [master];
GO
--Kill all DB connections
ALTER DATABASE SQLShack SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--take database in OFFLINE mode.
ALTER DATABASE SQLShack SET OFFLINE

Once the script is executed successfully, we can see the database ‘SQLShack’ in an offline state.

脚本成功执行后，我们可以看到数据库'SQLShack'处于离线状态。

We have already made the changes in the system catalog and renamed the files at the OS level. We need to bring the database online now with the below command. We need to execute the query in the same window or connection in which database offline script was executed. This script takes the database in online status and set the status as multi-user.

我们已经在系统目录中进行了更改，并在操作系统级别重命名了文件。我们现在需要使用以下命令使数据库联机。我们需要在执行数据库脱机脚本的同一窗口或连接中执行查询。该脚本使数据库处于联机状态，并将状态设置为多用户。

ALTER DATABASE SQLShack SET ONLINE
Go
ALTER DATABASE SQLShack SET MULTI_USER
Go

In SSMS, we can view database is online and accessible now.

在SSMS中，我们可以查看数据库是否联机并且现在可以访问。

We can see now that the physical file name of the database has been changed to reflect the new name in the database.

现在我们可以看到数据库的物理文件名已更改，以反映数据库中的新名称。

View the database properties in SSMS to see the filenames.

在SSMS中查看数据库属性以查看文件名。

We can change the database physical file name using ‘detach and attach’ method as well.

我们也可以使用“ detach and Attach”方法更改数据库物理文件名。

使用分离和附加数据库更改数据库物理文件名 (Use Detach and Attach database to change database physical file name)

Let us change the physical file name of the ‘SQLShack’ database using the detach and attach method. We can use it for SQL Server on Linux as well.

让我们使用分离和附加方法更改“ SQLShack”数据库的物理文件名。我们也可以将其用于Linux上SQL Server。

Right-click the database -> Tasks -> Detach

右键单击数据库->任务->分离

There might be active connections for the database; therefore, click on checkbox ‘drop connections’ and press ‘Enter’.

数据库可能有活动的连接；因此，请单击“断开连接”复选框，然后按“ Enter”。

We can see that the database does not exist in the SSMS now.

我们可以看到该数据库现在不存在于SSMS中。

Let us rename the files at the OS level using the terminal and ‘mv’ command.

让我们使用terminal和'mv'命令在操作系统级别上重命名文件。

# mv SQLShack_Demo.mdf SQLShack_Demo_new.mdf

＃mv SQLShack_Demo.mdf SQLShack_Demo_new.mdf

#mv SQLShack_Demo_log.ldf SQLShack_Demo_new_log.df

–View the modified file names with below command.

–使用以下命令查看修改后的文件名。

#ls -lrt

Once we have changed the file name, attach the database using SSMS ‘Attach’ wizard.

更改文件名后，使用SSMS“附加”向导附加数据库。

Right click on database node -> Attach.

右键单击数据库节点->附加。

Specify the .mdf file location and click OK.

指定.mdf文件位置，然后单击“确定”。

It searches for the old .ldf file in the location. We have already changed the file name, therefore, SQL Server could not locate the log file. It gives the message ‘Transaction log was not found’.

它在该位置中搜索旧的.ldf文件。我们已经更改了文件名，因此，SQL Server无法找到日志文件。它显示消息“未找到事务日志”。

In the current file path column, click on eclipse (…) and specify the newly renamed log file.

在当前文件路径列中，单击eclipse（…）并指定新重命名的日志文件。

Now, there is no error present so we can attach the database.

现在，不存在错误，因此我们可以附加数据库。

Once the database is attached, you can verify the physical file name using query or in SSMS.

附加数据库后，您可以使用查询或在SSMS中验证物理文件名。

结论 (Conclusion)

We learned various methods to change the physical and logical file name in a SQL Server database hosted in a Linux environment. I hope you enjoyed the article. Feel free to provide feedback in the comments below.

我们学习了各种方法来更改Linux环境中托管SQL Server数据库中的物理和逻辑文件名。希望您喜欢这篇文章。请随时在下面的评论中提供反馈。