RENAME SQL SERVER database name in Multiple user mode with killing all users' session (FORCE RENAME)) Posted by Unknown , Monday, September 9, 2013 at 8:45 AM, in

*Note: it has to be run against database master (select on master -> new query or add use master)

DECLARE @ID INT

DECLARE @MSG CHAR(8)

While 1 = 1

BEGIN

    Set RowCount 1

SELECT

  @id = spid

FROM

  Master..Sysprocesses P,

  Master..Sysdatabases D

WHERE

  D.Name= N'MYDATABASE'

AND

  D.dbid = P.dbid

    IF @@rowcount = 0

    break

    SELECT @msg = 'KILL ' + convert(char(8) ,@ID)

    Print @msg

    EXEC( @msg)

    --break

END

GO

Alter database MYDATABASE

set MULTI_USER

with rollback immediate

exec sp_renamedb N'MYDATABASE',N'MYDATABASE_STH_ELSE'