Renaming Logical and Physical file names in SQL Server on Linux
Renaming Logical and Physical file names in SQL Server on Linux
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolRenaming Logical and Physical file names in SQL Server on Linux
December 12, 2018 by Rajendra Gupta 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 1234567 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 123 select name as [Logical_name],physical_name as [physical file name]from sys.database_filesChange 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. 123456789 USE [SQLShack]GOALTER DATABASE [SQLShack] MODIFY FILE (NAME=N'SQLShack', NEWNAME=N'SQLShack_Demo')GOUSE [SQLShack]GOALTER 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. 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. 1234 ALTER DATABASE SQLShack MODIFY FILE (Name='SQLShack_Demo', FILENAME='/var/opt/mssql/data/SQLShack_Demo.mdf')GOALTER 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. 1234567 USE [master];GO--Kill all DB connectionsALTER DATABASE SQLShack SET SINGLE_USER WITH ROLLBACK IMMEDIATEGO--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. 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. 1234 ALTER DATABASE SQLShack SET ONLINEGoALTER DATABASE SQLShack SET MULTI_USERGo 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 Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022