Thursday, June 12, 2014

DataBase Renaming in SQL Server 2008 R2

I had renamed my Databases recently and wanted to share the experiences.

1) I have documented the steps need to be followed.
2) Prepared the scripts to rename the Databases.
3) Scripted the objects which are getting impacted by the renaming of DBs
3) Scripted the Jobs to Drop and Recreate.

Objects that are getting Impacted with the Renaming of DB:
Procedures
Views
Database Triggers
SQL Jobs

Finding Dependencies: Use the table "sys.sql_expression_dependencies" to find the cross Database dependencies. We have to make all dependent objects while renaming the Database

Renaming the DB:

-- Backup Database

--Set Database as a Single User 

ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

/* Change Logical File Name */

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'DBName_data'
, NEWNAME=N'DBName_data')
GO
ALTER DATABASE [DBName] MODIFY FILE (NAME=N'DBName_log'
, NEWNAME=N'DBName_log')
GO

/* Detach Current Database */

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'DBName'
GO

/* Rename the files manually

DBName_data.mdf
DBName_log.ldf

*/

/* Attach Renamed Database Online */

USE [master]
GO
CREATE DATABASE DBName ON
(FILENAME = N'path'),
(FILENAME = N'path')

FOR ATTACH
GO

/* Set Database to Multi User*/

ALTER DATABASE DBName SET MULTI_USER
GO

/* Identify Database File Names */

USE master
GO

SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'DBName')
GO

/*
Run the scripts in Views
Run the scripts in SPs

*/

Script out all objects by Alter statement and execute them after renaming the Database.
We cannot alter SQL Jobs hence take a script to drop and recreate and execute it.


No comments:

Post a Comment