Need to rename a production database from AppDB_test to AppDB_prod. I know the T-SQL rename command but is there more to it? The application connects by name and I do not want to break things quietly.
How to rename a SQL Server database safely?
Solved SQL & Databases
RK
Rachel Kim September 4, 2019
2 replies
6,280 views
Reviewed by moderators
The rename itself is one line but the surrounding steps matter more.
1
Rename needs exclusive access:
ALTER DATABASE AppDB_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;2
Rename:
ALTER DATABASE AppDB_test MODIFY NAME = AppDB_prod; then back to multi user.3
The logical name changed but the physical MDF and LDF filenames did not. Cosmetic, but rename those separately if it bothers you.
4
Update every connection string, linked server, job and maintenance plan that references the old name. This is where quiet breakage lives.
Rename was trivial, the connection string hunt was the real work. Two jobs referenced the old name. Solved.