rename sql server database

If you want to rename SQL Server database for any reason you can use Microsoft SQL Management Studio, simply right click and select rename. If you want to use TSQL then you can use sp_renamedb or with SQL Server 2005 and 2008 you should use Alter database modify name command.

Rename using TSQL

sp_renamedb 'sqldba' , 'sql_dba'

SQL Server 2005 and 2008

ALTER DATABASE sqldba MODIFY NAME = sql_dba

Simple TSQL Script to rename databases.

--alter the database to kill connections
ALTER DATABASE sqldba SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- Use sp_renamedb to change then name
sp_renamedb 'sqldba' , 'sql_dba'
GO
ALTER DATABASE sql_dba SET Multi_USER GO

Using Alter database modify name

-- Kill connections
ALTER DATABASE sqldba SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Alter the database name
ALTER DATABASE sqldba MODIFY NAME = sql_dba
-- Set database back to multiuser
ALTER DATABASE sql_dba SET Multi_USER

 

 



 4/28/2009


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.