CODEWITHSUNDEEP
sqlsql-servert-sqlssms
user4939121
user4939121

Reputation:

rename database name in sql server management studio 2014

I have a database called "ip_ent_site". And I wanna rename it to "ip_ent_site1" for example.

enter image description here

I have done right click and rename, it is keeping on failed. This is the error message:

enter image description here

Anyone can help?

Upvotes: 50

Views: 54598

Answers (11)

AgungCode.Com
AgungCode.Com

Reputation: 787

OFFLINE DATABASE

USE [master];
GO
--Disconnect all existing session.
ALTER DATABASE Datatbase_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

--Change database in to OFFLINE mode. ALTER DATABASE Datatbase_Name SET OFFLINE To rename Physical Database Files, use Open SQL Server Management Studio Folder or use the script below

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Rename MDF FILE dan Log

ALTER DATABASE [Datatbase_Name] MODIFY FILE (Name='Old_Mdf_file_Name', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL13.SQLMS2016\MSSQL\DATA\New_Mdf_file_Name.mdf')
ALTER DATABASE [Datatbase_Name] MODIFY FILE (Name='Old_log_file_Name', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL13.SQLMS2016\MSSQL\DATA\New_log_file_Name.ldf')

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLMS2016\MSSQL\DATA\Old_Mdf_file_Name.mdf", "New_Mdf_file_Name.mdf"'
GO

EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLMS2016\MSSQL\DATA\Old_log_file_Name.ldf", "New_log_file_Name.ldf"'
GO
ONLINE DATABASE

ALTER DATABASE [Datatbase_Name] SET ONLINE
Go
ALTER DATABASE [Datatbase_Name] SET MULTI_USER
Go

Upvotes: 2

Pani
Pani

Reputation: 1

Please try this,it will work.

ALTER DATABASE GWPRD_CMCC --old db name

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO ALTER DATABASE GWPRD_CMCC --old db name

MODIFY NAME = GWPRD_CMCC_1 --new db name

GO ALTER DATABASE GWPRD_CMCC_1 --new db name SET MULTI_USER GO

Upvotes: 0

Khan
Khan

Reputation: 11

Shouldn't we also change 'Logical File Name' as well?

ALTER DATABASE [testdb] MODIFY FILE (NAME=N'testdbold ', NEWNAME=N'testdb') GO ALTER DATABASE [testdb] MODIFY FILE (NAME=N'testdbold', NEWNAME=N'testdb_log') GO

Upvotes: 1

Dhana
Dhana

Reputation: 1658

Execute below query

USE master;
GO
sp_renamedb @dbname = 'old_name' , @newname = 'new_name'

Upvotes: 1

Md Ashikul Islam
Md Ashikul Islam

Reputation: 111

Rename SQLDatabase name Using Query

use [master] go

Alter Database Old_database_name Modify name = New_database_name

or

sp_renameDB 'old_dbname' , 'new_dbname'

Upvotes: 0

sqluser
sqluser

Reputation: 5672

Before renaming, set the database to single user mode MSDN

USE master;
GO
ALTER DATABASE ip_ent_site
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Then rename it

USE master
GO
ALTER DATABASE ip_ent_site
Modify Name = ip_ent_site1
GO

And then put it back to multi user mode

ALTER DATABASE ip_ent_site1
SET MULTI_USER;

Upvotes: 25

Danny Mahoney
Danny Mahoney

Reputation: 1255

To enable the ability to

right click and rename

the DB:

  1. Close all query windows
  2. Right click & rename DB

Upvotes: 5

Sachin Pakale
Sachin Pakale

Reputation: 302

This the simplest way to rename Database name. Just make sure you have closed all the query windows before running this command.

EXEC sp_renamedb 'old_Name', 'new_Name'

Upvotes: 1

Ranbeer M
Ranbeer M

Reputation: 141

The reason is because the database has to prevent any other connection/transaction to the db while you are renaming it.

A simple script to get a lock on the db:

ALTER DATABASE [ip_ent_site] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE [ip_ent_site] MODIFY NAME = [ip_ent_site_new]
GO

ALTER DATABASE [ip_ent_site_new] SET MULTI_USER;    
GO

Upvotes: 14

Raj
Raj

Reputation: 10843

That is because there are open transactions. If those transactions can be killed, then this can easily be done with this SQL

ALTER DATABASE ip_ent_site
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'ip_ent_site', 'new_db_name' ,'DATABASE';
GO
ALTER DATABASE new_db_name
SET MULTI_USER
GO

Upvotes: 52

Igor Borisenko
Igor Borisenko

Reputation: 3866

Execute system procedure sp_who2 to find out sessions which use the database and then close applications or kill sessions with kill command.

Upvotes: 0

Related Questions