Part 1 -- Restore a database to another instance...

,


This sproc MUST be created in the destination instance for the restore, so that all related users can be killed off and an exclusive lock put on the database to be overwritten by the restore.

--############################################################################################################################
--
--This script is being offered for public use and as such is being offered as untested and unverified.
--Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments 
--that are NOT under my control. 
--Redistribution or sale of kill_users_other_than_system_sp, in whole or in part, is prohibited! 
 
--Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, 
--or your company needs!
--
-- you will need to replace any item enclosed in <> with your own values!
--
--############################################################################################################################

USE [<your admin DB>]
GO
/****** Object:  StoredProcedure [<your admin schema>].[kill_users_other_than_system_sp]    Script Date: 04/22/2015 10:27:18 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [<your admin schema>].[kill_users_other_than_system_sp] 
--
--#############################################################
--
-- Author: Haden Kingsland
-- Date: 08/09/2010
-- Version: 01:00
--
-- Desc:To check for users other than myself and "sa" in a given (passed in)
--database. This is to be run prior to a restore to ensure that an exclusive lock
--can be taken on the database to do the restore.
--MUST exist in the MASTER DB of the receiving instance as remotely called from the
--restore_databases_to_another_instance_sp stored procedure on the source instance.
--
----#############################################################
--
-----------------------
-- Modification History
-----------------------
--
--#################################################################

(
@dbname sysname,
@p_error_description varchar(300) OUTPUT
)


AS

BEGIN

DECLARE @strSQL varchar(255),
@MailProfileName VARCHAR(50),
@spid varchar(10), 
@loginame varchar(255),
@program_name varchar(128),
@hostname varchar(20),
@MESSAGE_BODY varchar(2000),
@MESSAGE_BODY2 varchar(1000)

SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
WHERE name like '%<your mail profile name>%'

PRINT 'Killing ' + UPPER(@dbname) + ' Database Connections'
PRINT '----------------------------------------------------'
DECLARE LoginCursor CURSOR READ_ONLY
for select spid, loginame, program_name, hostname from master..sysprocesses
where UPPER(cmd) not in (
'LAZY WRITER', 
'LOG WRITER', 
'SIGNAL HANDLER', 
'LOCK MONITOR', 
'TASK MANAGER', 
'RESOURCE MONITOR',
'CHECKPOINT SLEEP',
'CHECKPOINT',
'BRKR TASK',
'BRKR EVENT HNDLR',
'TRACE QUEUE TASK')
AND db_name(dbid) = db_name(db_id()) -- @DBNAME
AND hostname != '<your machine>' -- not my pc!
and loginame <> 'sa'

OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @loginame, @program_name, @hostname

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

PRINT 'Killing user spid: ' + @spid + ' Name: ' + @loginame
SET @strSQL = 'KILL ' + @spid

BEGIN TRY

--set @output = ISNULL(@output,' ') + ' ' + @strsql
--print @output
--PRINT @strSQL

EXEC (@strSQL)
 
SET @MESSAGE_BODY = ' User: ' + @spid + ' ' + LTRIM(RTRIM(@loginame)) + ' was killed as part of the restore to database ... ' + @dbname + ' in instance ... ' + @@SERVERNAME + '. Program name: ' + LTRIM(RTRIM(@program_name)) + ' was running on host: ' + @hostname
SET @MESSAGE_BODY2 = ' User: ' + @spid + ' has been killed! in database ... ' + @dbname

EXEC msdb.dbo.sp_notify_operator 
@profile_name = @MailProfileName, 
@name=N'<your operator>',
@subject = @MESSAGE_BODY2, 
@body= @MESSAGE_BODY
 
END TRY

BEGIN CATCH
SELECT @p_error_description = ERROR_MESSAGE();
RETURN;
END CATCH

END

FETCH NEXT FROM LoginCursor INTO @spid, @loginame, @program_name, @hostname

END

CLOSE LoginCursor

DEALLOCATE LoginCursor

END
RETURN;

Rate

2 (1)

Share

Share

Rate

2 (1)