CK's BLOG
this site the web

RENAME SQL SERVER database name in Multiple user mode with killing all users' session (FORCE RENAME))

*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'

0 comments:

Post a Comment

Thanks for commenting...

 

Just An Information

I will updat useful information as frequent as I could...

Technology

Usage Policies