asked on

Rename SQL Server 2005 Database question

I'm trying to rename a database and am getting the following error...
Msg 924, Level 14, State 1, Line 1
Database 'AIMSNet_Test' is already open and can only have one user at a time.
Could you please assist me I am querying against master

I am following the below steps:

I have put the database in single user mode and am trying to rename using
EXEC sp_renamedb 'AIMSNet_Test' 'AIMSNet'
GO

Then detach the database -- Detach new database from SQL Server to rename the data files
EXEC sp_detach_db @dbname = 'AIMSNet'
GO

Then rename the data files using the command prompt...cd to data file locations then
--REN AIMSNet_Test_dat.mdf AIMSNet_dat.mdf
--REN AIMSNet_Test_log.ldf AIMSNet_log.ldf

--Reattach DB and data files
--EXEC sp_attach_db @dbname = ‘AIMSNet’
--, @filename1 = ‘D:\DATA\AIMSNet_dat.mdf’
--, @filename2 = ‘E:\LOGS\AIMSNet_log.ldf’
--GO

ASKER CERTIFIED SOLUTION

dba2dba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel_PL

You can also try:

ALTER DATABASE <oldname> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
EXEC sp_renamedb '<oldname>', '<newname>'
GO
ALTER DATABASE <newname> SET MULTI_USER
GO

Open in new window

Daniel_PL

Or, set it back to SINGLE_USER (in last step) if there's business need to left db in that mode.

chavis03

ASKER

Thanks so much for your help.....after I terminated the connections (there was a sleeping connection awaiting commands) I was able to complete everything I needed to do.