SQL Tip: Changing Database names along with file names (logical and physical)

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

Advertisements
About

IT professional with wider interest in technology

Tagged with:
Posted in security, sql, SQL Server, tsql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: