Home > SQL Challenge > Rename Database – Use sp_renamedb or alter database

Rename Database – Use sp_renamedb or alter database

A very good article: http://www.mssqltips.com/tip.asp?tip=1891

Problem
Need to rename database for various reasons.

Solution
Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn’t rename the Logical and Physical names of the underlying database files.

Use sp_rename, without changing the Logical and Physical names of the underlying database files

USE master
GO

ALTER DATABASE EAM_ETL
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

EXEC master..sp_renamedb ‘EAM_ETL’,’myEAM_ETL’
GO

ALTER DATABASE myEAM_ETL
SET MULTI_USER
GO

Identify Database File Names: they are not changed

USE master
GO

/* Identify Database File Names */
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’myEAM_ETL’)
GO

image

See the above article on how to use alter database to rename database name and also rename the Logical and Physical names of the underlying database files.

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

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: