Sql server change db file name

During the recent Comprehensive Database Performance Health Check , I was asked is there any way we can rename the logical database file name. Right after the blog post, I received another question where the user wanted to know if there is an easy way to rename the physical file.

In this blog post, we will learn about how to rename the physical database files name. SQL Server database files have two different names — 1 logical file name and 2 physical file name. You can see both the names of the files for your database when you run the following command. Now let us see a simple script which can change the physical file name.

Please make sure that you select the appropriate database context for your script when you want to change the physical name. If we want to rename any physical file, it is not possible to rename the file while the file is in the use. For the same reason, first, we will have to take the database offline. If you have previously ever taken any database offline, you might be familiar with the situation, where we are not able to take that offline due to open connection to the databases.

To take the database successfully offline, first we will move the database to single user mode and right after that, we will take the database offline.

Now we will have to go to your file system and rename to the new name. If the database you're renaming was set as the default database of a SQL Server login, they may encounter Error , Cannot open user default database. Use the following command to change the default to the renamed database:. Skip to main content. This browser is no longer supported.

Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. We can also see that the logical file names have been changed by running the below T-SQL command again. Is there a way to automate changing logical name for all databases on a given server? Rename logical database file name for a SQL Server database.

Related Articles. Rename SQL Server physical database file names using detach and attach method. How to rename a SQL Server database. How to determine SQL Server database transaction log usage. Understanding how SQL Server stores data in data files.

Copy a SQL Server database with just the objects and no data. How to stop and start SQL Server services. Popular Articles. Rolling up multiple rows into a single row and column for SQL Server data.

How to tell what SQL Server versions you are running. Resolving could not open a connection to SQL Server errors.