Here is a situation, i wanted to blog about. Renaming a database physical file. some may ask, why would anybody do it.  Well for the people who work like “alice in wonderland”, everything is streamlined without hiccups, but for a volatile environment like i work in anything is possible.  so back to the question of why would anybody rename the database, well there are certain situations as follows:

We will be taking the example of 2 databases ProjectDatabase_DEV and TEST as shown. one is a development database and the other is a test database as per the SDLC.

Developers working on limited resources use shared SQL Server environment and if the Project name has changed, so will be database name too, this is to streamline things a bit. When the objects are moved from _DEV to _TEST to production server, we have to take care of renaming and placing the database files appropriately:

Let us start with our databases: execute this command sp_helpdb ProjectDatabase_DEV

Now consider a scenario, where the team has decided to move this database into Production.

Steps you will take are take a most current full backup of the database and restore it on the production system.

(I am not going to talk about the users and logins and dbcc etc. this is just about renaming the db files)

Now we restore the database on the production server as ProjectDatabase.

There are  2 ways to restore the database : SSMS and T-SQL. I will use T-SQL for reference here:

Let us assume the database is restored to production using SSMS or T-SQL as

RESTORE database ProjectDatabase from disk = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ProjectDatabase_DEV.bak’

Now check the database on production server with sp_helpdb ProjectDatabase

you see the database Logical name still contains the _DEV as well as the Physical File Name.

So what do we do here: Things are simple: using T-SQL we change the logical and physical names as follows:

ALTER DATABASE ProjectDatabase
MODIFY FILE (NAME=’ProjectDatabase_DEV’, NEWNAME=’ProjectDatabase’)

After you execute the above statement, run sp_helpdb again, and here is the Output (see the Logical Name has changed)

Now we will change the Physical File name on the right:

ALTER DATABASE ProjectDatabase
MODIFY FILE (NAME=’ProjectDatabase’, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProjectDatabase.mdf’)

Check again with sp_helpdb and here is the output (see the physical file name has change)

But the Physical file residing in the Data Folder have not changed: WHY?. This is because the database file name has been changed in the system tables and not the file itself.

After a couple of week of SQL Server running smoothly, the sysadmins re-started the Server and suddently no one was able to access the database anymore. sp_helpdb produced no results and gave permission error etc. This is how it looked:

no one was able to see the objects inside the database. Even if you try to check out the properties you see this:

so to correct this was to stop SQL Server Service. Rename the Physical mdf and ldf files to the required names as were defined previously. Then start the SQL Server service, and LO!, you are able to access the Database.

The best practise is to check the Files names and paths while getting the RESTORE done either using T-SQL as below or SSMS.

RESTORE Database ProjectDatabase from disk = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ProjectDatabase_DEV.bak’
WITH REPLACE, – This is Optional and is only required on existing databases.
MOVE ‘Logical MDF Name’ to ‘Path to Physical FileName’,
MOVE ‘Logical LDF Name’ to ‘Path to Physical FileName’