Re: sp_renamedb

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Fox (_at_)
Date: 03/01/04 

Date: Mon, 1 Mar 2004 17:17:21 -0500

Thanks for the code.
There is nothing going on, that is what is so weird
about not being able to do this the simple way.
I have no concern about creating any problems
since no processes are running. But I did get
an error on the code and I am hoping you can
help me get past it.

Here is the code you sent and then the entries I added
and then the errors.

ALTER DATABASE OLD_DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_renamedb 'OLD_DBNAME', 'NEW_DBNAME'
ALTER DATABASE DANUT SET NEW_DBNAME WITH ROLLBACK IMMEDIATE

ALTER DATABASE OLD_DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC sp_renamedb 'MartialArtsArchive', 'FST'
ALTER DATABASE DANUT SET NEW_DBNAME WITH ROLLBACK IMMEDIATE

Server: Msg 102, Level 15, State 6, Line 3
Incorrect syntax near 'NEW_DBNAME'.
Server: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'IMMEDIATE'.

Regards and Thanks,
Fox

"Daniel P." <danutzp1@hotmail.comU> wrote in message
news:u$Uh4L9$DHA.3004@TK2MSFTNGP10.phx.gbl...
> It measn some other process uses that database.
> You need exclusive lock. You'll have to tell other people to log out, stop
> the processes that uyse the db etc.
>
> Or you can so something dangerous like this. This will kill all the other
> proceses, set the DB in single user mode, rename the db and put the db
back
> in multi user mode.
>
> WARNING: you may kill processes that are important so it will be your
> responsibility if it is in production and something breaks!!! You do this
at
> your own risk and you will take full responsibility!!!
>
> ALTER DATABASE OLD_DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> EXEC sp_renamedb 'OLD_DBNAME', 'NEW_DBNAME'
> ALTER DATABASE DANUT SET NEW_DBNAME WITH ROLLBACK IMMEDIATE
>
>
>
> "Fox" <fox @ connexions .net> wrote in message
> news:O25sHF9$DHA.2012@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I am trying to rename a database.
> >
> > I am getting an error telling me that
> > "the database cannot be exclusively locked ..."
> >
> > Can someone tell me what I can do so that
> > I can get sp_renamedb to rename this database?
> >
> > Thanks,
> > Fox
> >
> >
>
>

Relevant Pages

  • Re: sp_renamedb
    ... with FST in the ALTER statements, the same way you did in the EXEC ... ALTER DATABASE MartialArtsArchive SET SINGLE_USER WITH ROLLBACK IMMEDIATE ... ALTER DATABASE DANUT SET FST WITH ROLLBACK IMMEDIATE ...
    (microsoft.public.sqlserver.programming)
  • Re: sp_renamedb
    ... > ALTER DATABASE MartialArtsArchive SET SINGLE_USER WITH ROLLBACK IMMEDIATE ... >> with FST in the ALTER statements, the same way you did in the EXEC ... >> ALTER DATABASE DANUT SET FST WITH ROLLBACK IMMEDIATE ...
    (microsoft.public.sqlserver.programming)
  • Re: sp_renamedb
    ... ALTER DATABASE MartialArtsArchive SET SINGLE_USER WITH ROLLBACK IMMEDIATE ... ALTER DATABASE FST SET MULTI_USER WITH ROLLBACK IMMEDIATE ... > ALTER DATABASE MartialArtsArchive SET SINGLE_USER WITH ROLLBACK IMMEDIATE ...
    (microsoft.public.sqlserver.programming)
  • Re: Set Restricted_User
    ... Alter database dbName set single_user with rollback immediate ... > Alter Database dbname Set Restricted_User With Rollback Immediate ...
    (microsoft.public.sqlserver.security)
  • Re: sp_renamedb
    ... Fox ... > ALTER DATABASE MartialArtsArchive SET SINGLE_USER WITH ROLLBACK IMMEDIATE ... >> with FST in the ALTER statements, the same way you did in the EXEC ...
    (microsoft.public.sqlserver.programming)