Problem
Sometimes we need to rename our databases on SQL Server instances. When we rename a database, the database name changes but the physical database file names remain the same, so it becomes confusing to identify the corresponding database files for that database. As a best practice we should rename the physical database files to match the database name in such cases to avoid confusion. If you want to change the physical database file names of your database files then this tip is very useful.
Solution
As we all know, SQL Server database files have two names, one is known as the logical file name and the other is the physical file name. The physical file name is the name of the actual file in the Windows operating system.
When we rename a database, the database name changes but the physical database file name remains same. This can be confusing to identifying the corresponding database for the files. We should rename the database files to match the database name to avoid any confusion.
There are multiple ways to rename your physical database files of your SQL Server database. You can bring your database offline then you can execute ALTER statements after renaming the actual files or you can use the detach attach method to rename the files. In this tip, I will show you the step by step method to rename any physical database files by bringing the database OFFLINE and then running a few ALTER statements. I will explain the step by step method of the detach and attach method in my next tip.