Rajendra Gupta

Renaming Logical and Physical file names in SQL Server on Linux

December 12, 2018 by

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.

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.

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

First, let us create a sample database

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

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.

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.

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.

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

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_log.ldf to 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.

cd /var/opt/mssql/data

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

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.

Let us rename the file ‘SQLShack.mdf’ to ‘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.

Similarly, rename the ‘SQLShack_log.ldf’ file to ‘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.

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.

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.

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

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.

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

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.

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

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.

Right-click the database -> Tasks -> Detach

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

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

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

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

Right click on database node -> Attach.

Specify the .mdf file location and click OK.

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

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

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.

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.

Table of contents

SQL Server 2019 on Linux with Ubuntu and Azure Data Studio
SQL Server 2019 on Linux with a Docker container on Ubuntu
SQL Server 2019 on Linux with Ubuntu
SQL Server 2019 installation on Ubuntu without a Docker Container
Renaming Logical and Physical file names in SQL Server on Linux
Rename SQL Server instance on Ubuntu Linux
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL Server 2019 on Linux with Ubuntu and Azure Data Studio
  2. Renaming a SQL Server instance on Ubuntu Linux
  3. Changing SQL Dump file locations in SQL Server on Linux
  4. Change default database file and backup paths in SQL Server on Linux
  5. SQL Server 2019 on Linux with Ubuntu
168 Views