Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Nov 22, 2016

Renaming an SQL Server Database

You can use the following query to rename an SQL Database:

USE master
GO
ALTER DATABASE YOURDATABASENAME 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'YOURDATABASENAME','YOUR_NEW_DATABASE_NAME'
GO
ALTER DATABASE YOUR_NEW_DATABASE_NAME 
SET MULTI_USER 
GO

The only problem, well, not really a problem, is that although the Database's name has been changed, the physical Logical Name and File Name has not been renamed. You should also execute code similar to the following to rename the Logical Name and File Name as well:

ALTER DATABASE YOURDATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Change Logical File Name 
ALTER DATABASE [YOURDATABASENAME] MODIFY FILE (NAME=N'YOURDATABASENAME', NEWNAME=N'YOUR_NEW_DATABASE_NAME')
GO
ALTER DATABASE [YOURDATABASENAME] MODIFY FILE (NAME=N'YOURDATABASENAME_log', NEWNAME=N'YOUR_NEW_DATABASE_NAME_log')
GO

--Detach
GO
EXEC master.dbo.sp_detach_db @dbname = N'YOURDATABASENAME'
GO

--Rename
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\YOURDATABASENAME.mdf", "YOUR_NEW_DATABASE_NAME.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\YOURDATABASENAME_log.ldf", "YOUR_NEW_DATABASE_NAME_log.ldf"'
GO

--Attach
CREATE DATABASE YOUR_NEW_DATABASE_NAME ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\YOUR_NEW_DATABASE_NAME.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\YOUR_NEW_DATABASE_NAME_log.ldf' )
FOR ATTACH
GO
ALTER DATABASE YOUR_NEW_DATABASE_NAME SET MULTI_USER 
GO
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date