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 10:31:19 AM (US Mountain Standard Time, UTC-07:00)

Comments [6] Database | Sql Server |

Thursday, February 22, 2007 5:19:15 AM (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 4:11:56 AM (US Mountain Standard Time, UTC-07:00)

Cheers, this worked great for me! :)

Dave Curran

Thursday, December 27, 2007 1: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

Wednesday, February 25, 2009 7:18:50 AM (US Mountain Standard Time, UTC-07:00)

Thanks!

It worked great for me to!

With step 5 you mean to reset the Restrict Acces option set in step 1, not 2 :).

Bart van der Wal

Friday, October 16, 2009 10:54:52 AM (US Mountain Standard Time, UTC-07:00)

This works great! Thank you so much!

Vgt

Wednesday, October 28, 2009 6:26:07 AM (US Mountain Standard Time, UTC-07:00)

Great information -- note in SQL Server 2000 (at least -- the DB I am using at the moment), it will fail if you select "read only" so ignore that part -- otherwise, this is great information.

Michael

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

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