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.
You may also need: "Rename Logical Database File names of an Existing Database in SQL Server"
0 comments:
Post a Comment