Saturday, 30 September 2017

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

Usually, Physical database names are not changed after the database creation, unless there is a requirement for name change for the created database. Say, a database is created and it is renamed with a new name. The new database name will still point to the same physical paths of the originally created database and hence cause confusion to rectify the right database file names.

Unlike, the logical file names which can be altered using the ALTER command for the database, the physical database file names cannot be changed when the database is online. Very straightforward to know that these physical paths are already being used when the database is ONLINE. Hence, we will have to take the database offline and then proceed to alter the physical database file names. 

Before moving on to the methods of renaming the physical database files, let’s check the information of the database by using a simple stored proc.

EXEC sp_helpdb <Database_Name>

Example:

USE SQLArena_Test
EXEC sp_helpdb SQLArena_Test

From the above stored proc, if we have to gather the physical data filenames and logical filenames of the database, proceed with the query as:

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


Take database offline and then rename the Physical Database Files

In order to change the physical filename from the screenshot, let’s first disconnect all the opened session in the SQL Server and take the database OFFLINE.

USE SQLARENA_TEST
GO
ALTER DATABASE SQLARENA_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE SQLARENA_TEST SET OFFLINE

Check if the database is offline or online with the command as:

SELECT NAME, STATE_DESC FROM SYS.DATABASES WHERE NAME='SQLARENA_TEST'



Now, all the sessions and process connected to SQLArena_TEST database is disconnected and taken the database to offline mode. Let’s ALTER the physical filenames by "_CHANGED" :

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_CHANGED.mdf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_1', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_1_CHANGED.ndf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_2', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_2_CHANGED.ndf')
GO
ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_log', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_log_CHANGED.ldf')
GO

Set the database to online and now you can now check the database information and the status of the databases as:

EXEC sp_helpdb SQLArena_Test
GO
USE SQLARENA_TEST
GO
ALTER DATABASE SQLARENA_TEST SET SINGLE_USER
GO
ALTER DATABASE SQLARENA_TEST SET OFFLINE

While using these scripts in production should be with caution, since backups may fail if the logical filenames are incorrectly assigned. 

0 comments: