Just renamed a database for a customer, and I wanted to share the logic with all of you. When we rename a database, it only effects the database name itself. It does not change the data or log file names. I HATE it when the data/log file names don't match the db name! Consistently named objects are faaaar easier to manage, imo. :-) You can use this method to rename the database AND its data and log files. Easy peasy. In this example, the 'InformDVODV' database is renamed to 'InformDVOPD'.
-- get your db filenames
USE InformDVODV; -- current database name
EXEC sp_helpfile
-- physical names and paths
-- only change the data/log
file names in the FILENAME value
ALTER DATABASE InformDVODV -- don't change
MODIFY FILE (
NAME = 'InformDVODV', -- don't change
FILENAME = 'E:\MSSQL\DATA\InformDVOPD.mdf' -- change this
);
ALTER DATABASE InformDVODV -- don't change
MODIFY FILE (
NAME = 'InformDVODV_log', -- don't change
FILENAME = 'O:\MSSQL\Log\InformDVOPD_log.ldf' -- change this
);
-- logical names
-- only change the NEWNAME value
ALTER DATABASE InformDVODV MODIFY FILE (
NAME = InformDVODV,
NEWNAME = InformDVOPD -- change this
);
ALTER DATABASE InformDVODV MODIFY FILE (
NAME = InformDVODV_log,
NEWNAME = InformDVOPD_log -- change this
);
-- now we rename the database
USE master;
GO
ALTER DATABASE InformDVODV
Modify Name = InformDVOPD;
GO
-- check your newly renamed files
USE InformDVOPD; -- new db name
EXEC sp_helpfile
That's it! Important to know, you cannot do this if users are in the database. If there are active connections, you will need to kill them before attempting the rename. Run this to kill any connections by setting the database to single user mode:
USE [master];
GO
-- disconnect all existing sessions
ALTER DATABASE InformDVODV SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- change
to multi-user
ALTER DATABASE InformDVOPD SET MULTI_USER
GO
That's it! Follow the sequence and double-check your filenames, this will work fine. I would recommend taking a look at both of these for more details as well:
ALTER DATABASE
RENAME DATABASE
This is very helpful. I have one addition, which is to physically rename the files where they reside (Data and Log files). Do do this the database must also be offline, and the user has Local-Admin permissions.
ReplyDeleteI put this in between the physical-file rename, and the logical-file rename. It could be after both.
-- Physically Rename the Database Data/Log files.
-- Database must be OFFLINE during this.
USE Master
GO
ALTER DATABASE [SampleDB] SET OFFLINE
GO
---- -----------------------------------------------------------------
---- NOTE: Now physically rename the Data/Log filenames on the system.
---- -----------------------------------------------------------------
ALTER DATABASE [SampleDB] SET ONLINE
GO
Thanks, Michael Barash (Senior-SQL-Developer/DBA)