September 17, 2002 at 10:47 am
Is it possible to rename a database name, and if so, how?
Thanks!!!
September 17, 2002 at 10:59 am
Thanks, I just tried your suggestion but now I get the following error message...
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_renamedb'.
When I open the stored proc in the Master db, it is empty.
Can you please copy/paste here please.
Thanks,
Brian
September 17, 2002 at 1:05 pm
I tried to modify this sp but I get an error message that states "Ad hoc updates to system catalogs are not enabled. The system admin must reconfigure SQL Server to allow this."
Could I possibly just create a new database (with desired name) and move all tables and stored procs from the existing database into this new database and lastly remove the old database? Is this possible?
September 17, 2002 at 1:10 pm
Just take a database backup of the database you wish to rename, and restore it to the database name you want. I do this all the time in 7.0, or 2000. I'm guessing this might also work in older version of SQL Server.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2002 at 1:23 pm
aRE YOU RUNNING sql 7 or 2000? sp_renamedb is a basic store proc in the master db. I copied the statement in case you need it, but you can go to BOL and check it out. It should work when you use the syntax properly. Don't forget the quotes, otherwise it will fail. Good luck
EXEC sp_renamedb 'db1', 'db2'
There you go
reate procedure sp_renamedb --- 1996/08/20 13:52
@dbname sysname,/* old (current) db name */
@newname sysname/* new name we want to call it */
as
-- Use sp_rename instead.
declare @objid int/* object id of the thing to rename */
declare @bitdesc varchar(30)/* bit description for the db */
declare @curdbid int/* id of database to be changed */
declare @execstring nvarchar (4000)
/*
** If we're in a transaction, disallow this since it might make recovery
** impossible.
*/
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_renamedb')
return (1)
end
/*
** Only the SA can do this.
*/
if not (is_srvrolemember('dbcreator') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end
/*
** Make sure the database exists.
*/
if not exists (select * from master.dbo.sysdatabases where name = @dbname)
begin
raiserror(15010,-1,-1,@dbname)
return (1)
end
/*
** Make sure that the @newname db doesn't already exist.
*/
if exists (select * from master.dbo.sysdatabases where name = @newname)
begin
raiserror(15032,-1,-1,@newname)
return (1)
end
/*
** Check to see that the @newname is valid.
*/
declare @returncode int
exec @returncode = sp_validname @newname
if @returncode <> 0
begin
raiserror(15224,-1,15,@newname)
return(1)
end
/*
** Don't allow the names of master, tempdb, and model to be changed.
*/
if @dbname in ('master', 'model', 'tempdb')
begin
raiserror(15227,-1,-1,@dbname)
return (1)
end
select @execstring = 'ALTER DATABASE '
+ quotename( @dbname , '[')
+ ' MODIFY NAME = '
+ quotename( @newname , '[')
exec (@execstring)
if @@error <> 0
begin
-- No need to raiserror as the CREATE DATABASE will do so
return(1)
end
return (0) -- sp_renamedb
GO
September 17, 2002 at 1:29 pm
I'm not on the server with the back-up device and have no control over this -- I get the message "Cannot open backup device..."
Any other ideas?
September 17, 2002 at 1:30 pm
I'm not on the server with the back-up device and have no control over this -- I get the message "Cannot open backup device..."
Any other ideas?
September 17, 2002 at 1:35 pm
You might try creating a backup device that points to the network backup device, and then restore from that backup device you defined.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 19, 2002 at 9:48 am
ALTER DATABASE DBA SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
SP_RENAMEDB DBA,DBA1
ALTER DATABASE DBA1 SET MULTI_USER WITH ROLLBACK IMMEDIATE;
Hope this should work for you.
Thanks,
Vijay
September 20, 2002 at 7:28 am
Does anyone have the SQL Server 7 syntax for the sp_renamedb stored proc that they can post here?
Thanks!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply