Sometimes you need to rename a databases.
Although it seems pretty easy to rename it on the surface.
However for long term maintainability it is crucial that the underlying logical files and physical files also reflect the changes.
Without the deeper changes to the files it is easy to get confused and get into trouble even by the same person in future even if the change was documented.
Following script snippets help in this regards. It can additionally and easily help in moving files for user database with little to no modification.
-- Confirm the files related to the database you are working with. -- You might have to change the script depending on the files the following query returns SELECT * FROM sys.master_files WHERE database_id = DB_ID(N'ExistingDatabaseName') /* Set Database as a Single User In case of issues try to make sure that the applications, users and services trying to connect to this database are disconnected. You might have to use activity monitori in SSMS to make sure no connections are on the database going through this process of renaming. */ ALTER DATABASE ExistingDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE /* Change Logical File Name Logical file name ne */ ALTER DATABASE [ExistingDatabaseName] MODIFY FILE (NAME = 'ExistingDatabaseName', NEWNAME = NewDatabaseName); GO ALTER DATABASE [ExistingDatabaseName] MODIFY FILE (NAME = 'ExistingDatabaseName_log', NEWNAME = NewDatabaseName_log); GO /* Detach Current Database to detach database. */ USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'ExistingDatabaseName' GO --/* Enable Command Shell */ USE master GO sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO /* 0 = Disabled , 1 = Enabled */ sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO /* Rename Physical Files If The following errors out. Do not worry and rename the files appropriately with Windows explorer or DOS command line yourself. */ USE [master] GO EXEC xp_cmdshell 'RENAME "D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ExistingDatabaseName.mdf", "NewDatabaseName.mdf"' GO EXEC xp_cmdshell 'RENAME "E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ExistingDatabaseName_log.ldf", "NewDatabaseName_log.ldf"' GO /* Attach Renamed Database Online */ USE [master] GO CREATE DATABASE NewDatabaseName ON ( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabaseName.mdf' ), ( FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\NewDatabaseName_log.ldf' ) FOR ATTACH GO ALTER DATABASE NewDatabaseName SET MULTI_USER GO --/* Disable Command Shell */ USE master GO /* 0 = Disabled , 1 = Enabled */ sp_configure 'xp_cmdshell', 0 GO RECONFIGURE WITH OVERRIDE GO sp_configure 'show advanced options' GO RECONFIGURE WITH OVERRIDE GO -- Rechecking file changes to verify. SELECT * FROM sys.master_files WHERE database_id = DB_ID(N'NewDatabaseName') GO /* Rename User */ USE [master] ALTER LOGIN [ExistingDatabaseNameUser] WITH NAME = [NewDatabaseNameUser]; GO /* Add db_owner role to renamed user in case if thats not already assigned.*/ USE [NewDatabaseName] GO ALTER ROLE [db_owner] ADD MEMBER [NewDatabaseNameUser] GO