Rename Logical Database File names of an Existing Database in SQL Server

Basically, in SQL Server there are two database files, one as physical file name and the other as logical file name. We will be much focusing on the logical file name of a database. Generally, when a databases is created, one primary data file, two secondary data files and one logical files are created. The logical files in a database helps holding the active transaction of a database. We should see that the logical files are always kept unique to resist from unnecessary confusions.

Usually, in SQL server while managing a database in ethical form, just one logical file is created. In case if there are too much transactions in a database, which keeps them filled always, then probabilities could be to create a new logical file.

 To view the information of a database, use the below stored proc:

EXEC sp_helpdb <Database_Name>

Example: 

USE SQLArena_Test
GO
sp_helpdb SQLArena_Test

Following the above query can show the details of the database mentioned. In order to select the logical file name from the database, use the below query:

select file_id, NAME as [LOGICAL_FILE_NAME], PHYSICAL_NAME
from sys.database_files


In order to rename the logical file names from the mentioned screenshot to a new name, let’s follow the script below:

Rename SQLArena_Log to SQLArena_Log_1, along with change in physical data files and indexes.

USE SQLArena_Test
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Data1, NEWNAME=SQLArena_Data_1)
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Log, NEWNAME=SQLArena_Log_1)
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Data2, NEWNAME=SQLArena_Data_2)
GO
Alter Database [SQLArena_Test] Modify file (Name=SQLArena_Indexes,NEWNAME=SQLArena_Indexes)

0 comments: