UrbanPro
true

Learn MS SQL from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Search in

Rename The Logical Name Of SQL Server Database Files Using T-SQL

Amitava Majumder
31/07/2017 0 0

Rename the logical name of SQL Server database files using T-SQL:

1. Problem:

SQL Server database files have two names: one is the logical file name and the other is the physical file name. The logical file name is used with some T-SQL commands, such as DBCC SHRINKFILE. If you want to change the logical file names of your database files then this tip will be very useful. This way you can keep your logical database file names unique on your SQL Server instance.

Since you may want to move this database on a production server, or change the database name because of any reason, you may also want to change the names of the database files. You may want the database names consistent with the database names or ant other rules you obey for standardization.

2. Solution:

It is not mandatory to keep logical file names unique on a SQL Server instance, but ideally we should keep them unique to avoid any confusion.

When we restore a database, the logical database file names don't change and are the same as the source database. So if you are restoring to multiple databases with the same backup file, the logical file names will be the same for all of the restored databases.

Let's start with creating a database having three data files and a log file then we will change the logical file name of these database files to show the step by step method.

Rename the logical SQL Server database file name using T-SQL

Step 1: First we will create a database named "Test_DB" with 1 data files and 5 log files and one log file by running the below T-SQL code. You can change the name of the database, file path, file names, size and file growth according to your needs.

USE MASTER;

GO

CREATE DATABASE Test_DB

ON

( NAME = Test_DB,

FILENAME = 'E:\Data\Test_DB.mdf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB )

LOG ON

( NAME = Test_DB_log1,

FILENAME = 'E:\Data\Test_DB_log1.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log2,

FILENAME = 'E:\Data\Test_DB_log2.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log3,

FILENAME = 'E:\Data\Test_DB_log3.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log4,

FILENAME = 'E:\Data\Test_DB_log4.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ),

( NAME = Test_DB_log5,

FILENAME = 'E:\Data\Test_DB_log5.ldf',

SIZE = 204800KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 102400KB ) ;

GO

Step 2: Next I created a backup of the database.

BACKUP DATABASE Test_DB TO DISK = 'D:\Data\Backup\Test_DB.bak'

GO

I ran the below T-SQL command to get the logical file names of database file for the database TEST_DB.

USE Test_DB

GO

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files

file_id

logical_file_name

physical_name

1.

Test_DB

E:\Data\Test_DB.mdf

2.

Test_DB_log1

E:\Data\Test_DB_log1.ldf

3.

Test_DB_log2

E:\Data\Test_DB_log2.ldf

4.

Test_DB_log3

E:\Data\Test_DB_log3.ldf

5.

Test_DB_log4

E:\Data\Test_DB_log4.ldf

6.

Test_DB_log5

E:\Data\Test_DB_log5.ldf

Step 3: Now we will change the logical file name for the log data files for database "Test_DB" from "Test_DB_log" to "Test_DB_New2_log.ldf" by running an ALTER DATABASE statement using MODIFY FILE according to their file_id. To modify the logical name of a data file or log file, we will specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. Run the below command to create a procedure apply this change.

CREATE PROCEDURE Rename_Logical_Name

(

@Database sysname, @New_name sysname

)

AS

BEGIN

DECLARE @cmd1 nvarchar(200)

DECLARE @cmd2 nvarchar(200)

DECLARE @ldffile sysname

DECLARE @id sysname

DECLARE theCursor CURSOR

FOR (SELECT name,FILE_ID FROM sys.database_files WHERE type = 1)

OPEN theCursor

FETCH NEXT FROM theCursor INTO @ldffile,@id

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd2 = 'ALTER DATABASE ' +@Database+' MODIFY FILE (NAME= '''+ @ldffile + ''', NEWNAME = '''+@ New_name +@ID+'_log.ldf'')'

EXEC (@cmd2)

FETCH NEXT FROM theCursor INTO @ldffile,@id

END

CLOSE theCursor

DEALLOCATE theCursor

--PRINT @cmd1

--PRINT @cmd2

--EXEC (@cmd1)

EXEC (@cmd2);

END

EXEC Rename_Logical_Name @Database = 'Test_DB',@New_name = 'Test_DB_New'

Step 4: Now we will check the logical file names to verify our change we made in the last step. Run the command below to check the logical names.

SELECT file_id, name as [logical_file_name],physical_name

from sys.database_files WHERE type = 1

We can see the logical file names has been changed without any downtime of the database.

file_id

logical_file_name

physical_name

2.

Test_DB_New2_log.ldf

E:\Data\Test_DB_log1.ldf

3.

Test_DB_New3_log.ldf

E:\Data\Test_DB_log2.ldf

4.

Test_DB_New4_log.ldf

E:\Data\Test_DB_log3.ldf

5.

Test_DB_New5_log.ldf

E:\Data\Test_DB_log4.ldf

6.

Test_DB_New6_log.ldf

E:\Data\Test_DB_log5.ldf

 

NOTE: Do not change logical names of any data file of your production database until it is required or needed.

0 Dislike
Follow 0

Please Enter a comment

Submit

Other Lessons for You

Do You Give Enough Importance In Writing Main () In C Language?
I am sure; you guys are quite familiar with C programming, especially, while it comes to write the first line of your main function. In my class, I have seen many of my students are writing main function...

10 Study Tips to Achieve your Goals
This is particularly true of students that are looking to make the most of their study time and get better grades at Organization. 1. Set Study Goals: There is lots of credible research suggesting that...

5 Tips For Improving Your Documentation Immediately.
Tip 1) Quit it with the Passive Voice The passive voice is a plague on effective documentation. It reduces its clarity, its consistency, and the efficiency and tightness of the writing. The passive voice...

Cursors In SQL Server
First thing first Usage of Cursors is not encouraged in SQL Server as they are slow. You may go with While loop if you need to iterate through a recordset. Cursor is a database object to retrieve data...

Essential SQL Tips For Developers And For MS SQL DBA
10 Essential SQL Tips for Developers: SQL is yet another essential language for developers wishing to create data-driven websites. However, many developers are unfamiliar with various aspects of SQL;...
X

Looking for MS SQL Classes?

The best tutors for MS SQL Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn MS SQL with the Best Tutors

The best Tutors for MS SQL Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more