How to Rename a SQL Server Database Safely (and Why You Can’t Access It Afterwards)

Renaming a database is a one-line command. But the name lives in many places outside the database: application connection strings, logins’ default databases, Agent jobs, linked server queries, views and procedures written with three-part names. The “I can’t access the database” complaints after the command usually come from these places.

This article covers how to rename a database safely, the common errors, and what to check after the rename.

The Rename Command

sp_renamedb is deprecated; the current method is ALTER DATABASE ... MODIFY NAME. A rename needs exclusive access to the database; if connections are open, you have to end them first:

USE master;

-- Close open connections and take exclusive access (open transactions are rolled back)
ALTER DATABASE SalesDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE SalesDB MODIFY NAME = SalesDB_Archive;

ALTER DATABASE SalesDB_Archive SET MULTI_USER;

If exclusive access can’t be obtained, you’ll see this error:

File Names Don’t Change

Even though the database name changes, the logical file names (SalesDB_Data) and the physical files on disk (SalesDB.mdf) keep their old names. For consistency, you can change them too:

-- Logical names (can be done while online)
ALTER DATABASE SalesDB_Archive MODIFY FILE (NAME = N'SalesDB_Data', NEWNAME = N'SalesDB_Archive_Data');
ALTER DATABASE SalesDB_Archive MODIFY FILE (NAME = N'SalesDB_Log',  NEWNAME = N'SalesDB_Archive_Log');

-- Physical files: define the new paths first, then take it offline and rename the files
ALTER DATABASE SalesDB_Archive MODIFY FILE (NAME = N'SalesDB_Archive_Data', FILENAME = N'E:\Data\SalesDB_Archive.mdf');
ALTER DATABASE SalesDB_Archive MODIFY FILE (NAME = N'SalesDB_Archive_Log',  FILENAME = N'L:\Log\SalesDB_Archive_log.ldf');
ALTER DATABASE SalesDB_Archive SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Rename the files in Windows to match the new names, then:
ALTER DATABASE SalesDB_Archive SET ONLINE;

When renaming the files, make sure the SQL Server service account keeps its permissions on them.

“I Can’t Get In” After the Rename

The login’s default database

Logins whose default database is the old name fail to log in:

-- Logins whose default database no longer exists
SELECT name, default_database_name
FROM sys.server_principals
WHERE default_database_name IS NOT NULL
  AND DB_ID(default_database_name) IS NULL;

ALTER LOGIN AppUser WITH DEFAULT_DATABASE = SalesDB_Archive;

Other places to check

• Application connection strings: the Initial Catalog or Database values.
• Agent jobs: the database job steps run in, and names inside commands.
• Three-part names: references like SalesDB.dbo.Table in views, procedures and synonyms in other databases. Use the queries in our dependency guide to find them.
• Linked server queries: four-part name references to this database from other servers.
• Backup and maintenance jobs: scripts and file names that hard-code the database name.
• SSIS, SSRS and ETL connections.

A Less Disruptive Path: Synonyms or a Transition Period

When the name has to change but you can’t update every dependency at once, update the code that references the old name gradually. Using synonyms for references from other databases means a future rename only needs an update in one place.

Checklist

• The change happens in a maintenance window, coordinated with application teams.
• ALTER DATABASE … MODIFY NAME was used, not sp_renamedb.
• Login default databases were updated.
• Connection strings, Agent jobs, and three- and four-part names were checked.
• AG, mirroring, replication and log shipping were planned separately.
• Logical and physical file names were updated if needed.