Site icon Information2 | Data Management & Recovery Pioneer

[3 Safe Methods] How to Rename a SQL Server Database

Emma

There are several reasons to rename a SQL Server database — fixing a naming mistake, reflecting a project rebrand, or reorganizing environments. The process is straightforward, but it’s not just a label change.

This guide covers three methods to rename a database in SQL Server: using SSMS, running a T-SQL command, and detach-and-attach. Each suits a different scenario, so you can choose what fits your situation.

Can You Rename a SQL Server Database

Yes, SQL Server supports renaming databases. When you rename one, the logical name — the name shown in Object Explorer and sys.databases — is updated. All new queries and management tasks will use the new name from that point on.

That said, a few things don’t change automatically:

Note: SQL Server requires an exclusive lock to rename a database, so no other users can be connected during the process.

How to Rename a SQL Server Database

There are a few ways to rename a database in SQL Server. The right method depends on whether you need to change just the logical name or the physical files as well.

Method 1: Rename a SQL Server Database Using SSMS

SSMS is the quickest option if you have direct access to the management console and prefer a GUI over writing code.

  1. Open SSMS and connect to your SQL Server instance.
  2. In Object Explorer, expand the Databases folder.
  3. Right-click the target database and select Rename.
  4. Type the new name and press Enter.

Tip: If the rename fails, active connections are likely the cause. Make sure no users or applications are connected to the database before trying again.

Method 2: Rename a SQL Server Database Using T-SQL

T-SQL is the better choice for scripted or remote workflows. You may come across sp_renamedb in older scripts, but it’s deprecated — use ALTER DATABASE instead. It’s the current standard for this operation in SQL Server 2012 and later.

If no one is connected to the database, a single command is enough:

ALTER DATABASE [OldDatabaseName] MODIFY NAME = [NewDatabaseName];

If there are active connections, switch to single-user mode first, then rename, then restore access:

ALTER DATABASE [OldDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [OldDatabaseName] MODIFY NAME = [NewDatabaseName];
ALTER DATABASE [NewDatabaseName] SET MULTI_USER;
Note: sp_renamedb is deprecated — ALTER DATABASE is the recommended syntax going forward.

Method 3: Rename a SQL Server Database with Detach&Attach

Use this method when you also want to rename the physical .mdf and .ldf files on disk, not just the logical database name.

  1. Back up the database before doing anything else.
  2. In SSMS, right-click the database, go to Tasks, and select Detach. Check Drop Connections and click OK.
  3. Go to the data folder on disk and rename the physical files (e.g., OldName.mdf → NewName.mdf, OldName.ldf → NewName.ldf).
  4. In SSMS, right-click Databases and select Attach.
  5. Click Add and select your renamed .mdf file. In the Attach As column, the name defaults to the old logical name — change it to your new database name here.
  6. Click OK to complete the attach.
  7. Once attached, update the logical file names to match the new database name:
ALTER DATABASE [NewDatabaseName] MODIFY FILE (NAME = [OldLogicalName], NEWNAME = [NewLogicalName]);

 

Common Errors & Troubleshooting

Even a straightforward database rename can run into issues. Here are three common ones and how to fix them.

Error 1: Database is currently in use / cannot open database

SQL Server needs an exclusive lock to rename a database, so any active connection will block the operation. Switch to single-user mode first to force existing connections to drop:

USE master;
ALTER DATABASE [OldName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [OldName] MODIFY NAME = [NewName];
ALTER DATABASE [NewName] SET MULTI_USER;

 

Error 2: Deprecated syntax warning (sp_renamedb)

This warning appears when using the old sp_renamedb procedure. Replace it with ALTER DATABASE ... MODIFY NAME — it’s the supported syntax for SQL Server 2012 and later.

Error 3: Physical file access denied during detach-attach

This comes up during the detach-and-attach process. The SQL Server service account likely doesn’t have the right permissions on the folder where the renamed files are stored. Go to the folder’s Security settings in Windows Explorer and grant the SQL Server service account Modify or Full Control access.

Protect Your SQL Server Database With i2Backup

Renaming a database is a structural change. If connection strings or permissions aren’t updated correctly, it can lead to unexpected downtime with no easy way back. Before making any changes to your environment, taking a fresh backup is standard practice — and that’s where i2Backup comes in.

i2Backup is a data protection platform built for critical database workloads, including SQL Server, Oracle, and MySQL.

Key Features of i2Backup

With a distributed architecture and centralized management console, i2Backup simplifies data governance across complex environments — so your SQL Server instances stay resilient and recoverable when it matters most.

FREE Trial for 60-Day

FAQs

Q1: Does renaming a SQL Server database affect existing application connections?

Yes. Renaming only updates the logical name in SQL Server. Any connection strings, scripts, or linked server references that use the old name will stop working and need to be updated manually.

 

Q2: How to change db_name in SQL Server?

Use ALTER DATABASE [OldName] MODIFY NAME = [NewName]; to update the database name. If other users are connected, set the database to single-user mode first to avoid lock errors.

 

Q3: How to rename a database table in SQL Server?

Use the sp_rename procedure: EXEC sp_rename 'OldTableName', 'NewTableName'; Note that this just renames the table — any stored procedures or views referencing the old name will need to be updated manually.

 

Q4: Do I need to take the database offline to rename it?

Not always. Using SSMS or ALTER DATABASE, you can rename without taking it offline — just make sure there are no active connections. The detach-and-attach method does require the database to go offline, but it’s an effective way to also rename the physical files.

 

Q5: Can I rename a system database in SQL Server?

No. System databases like master, model, and tempdb cannot be renamed. They are required for SQL Server to function, and attempting to rename them will result in an error.

 

Q6: Does renaming a database also rename the .mdf and .ldf files?

No. Only the logical name changes. To rename the physical files, use the detach-and-attach method — detach the database, rename the files on disk, then reattach under the new name.

 

Q7: Can I undo a database rename in SQL Server?

Yes. Simply run ALTER DATABASE [NewName] MODIFY NAME = [OldName]; to revert. If you also renamed physical files, you’ll need to go through the detach-and-attach process again.

 

Conclusion

Renaming a SQL Server database is manageable once you know which method fits your situation. SSMS works well for quick, one-off changes. T-SQL gives you more control and is better suited for scripted workflows. The detach-and-attach method is the right choice when you also need to rename the physical files on disk.

Whichever approach you take, always back up your database first — and don’t forget to update connection strings and any scripts that reference the old name after the rename is done.

For a reliable backup solution before making structural changes like this, i2Backup keeps your SQL Server data protected and recoverable at every step.

Exit mobile version