The Johnnynine Weblog - How to rename a SQL Server Database

Tuesday, September 06, 2005

You can not rename a SQL Server database from Enterprise Manager, but you can with the sp_renamedb stored procedure. You must first set the database to singe user.

1. Set the database to Single User by right clicking the database in Enterprise Manager, select Properties, select the Options Tab, check the Restrict Access checkbox, and select Read-Only, then click OK.
2. Close Enterprise Manager (since we can only have 1 user accessing it at a time now).
3. Open Query Analyser.
4. Type: sp_renamedb <oldname>, <newname>
5. Don't forget to reset the Restrict Access settings on the database that you changed in step 2.

Tuesday, September 06, 2005 5:31:19 PM (US Mountain Standard Time, UTC-07:00)

Comments [5] Database | Sql Server |

Tuesday, January 30, 2007 6:27:44 PM (US Mountain Standard Time, UTC-07:00)

Howdy Folks. I was wondering if anyone had any details on the Eastern re-union that is being held in Moncton in 2007. Cheers, Dan

Kathryn

Tuesday, January 30, 2007 6:27:45 PM (US Mountain Standard Time, UTC-07:00)

I am trying to contact a Sandra Steeves, originally from Nova Scotia. Daughter of Jean Knickle Steeves. May be living in Ontario presently.

Javier

Thursday, February 22, 2007 12:19:15 PM (US Mountain Standard Time, UTC-07:00)

When I follow the above procedure in SQL Server 2000, I get:
Server: Msg 902, Level 16, State 1, Line 1
To change the NAME, the database must be in state in which a checkpoint can be executed.

I closed my Access .adp project that is on top of the SQL Server 2000 database, disconnected/reconnected, called CHECKPOINT, but still get the same msg.

HeidelbergMan

Friday, March 02, 2007 11:11:56 AM (US Mountain Standard Time, UTC-07:00)

Cheers, this worked great for me! :)

Dave Curran

Thursday, December 27, 2007 8:09:20 PM (US Mountain Standard Time, UTC-07:00)

Please note that in order to rename the database, it must NOT be read-only. Therefore, do NOT select read-only as suggested.

Victor Roman

Name
E-mail	(will show your gravatar icon)
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):