Database Force Restore

,

This stored Procedure can be used to force restores the database by killing all the process running over this database.

USE master

GO
IF EXISTS (SELECT * FROM sysobjects 
		WHERE id = object_id(N'[dbo].[spForceRestoreDB]') 
		AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spForceRestoreDB]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO
/*---------------------------------------------------------------------------
    Stored Procedure Name	:	spForceRestoreDB 

    Return Value		: 	NIL

    Parameters			: 	@DatabaseName varchar(50) - Database Name 
    					@BackupFile varchar(255)  - file from which Database
    								    has to be restored

    Remarks			:	This SP will force restore the Database by killing all
    					process using this database.
---------------------------------------------------------------------------*/
CREATE PROCEDURE spForceRestoreDB
    @DatabaseName varchar(50),
    @BackupFile varchar(255)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON	
	DECLARE @exists int

	IF NOT EXISTS( select name from master.dbo.sysdatabases WHERE name = @DatabaseName)
	BEGIN
	PRINT '		Database ' + @DatabaseName + ' not found '
	PRINT '		Enter valid Datbase name'
	RETURN
	END

	EXEC master.dbo.xp_fileexist @BackupFile , @exists OUTPUT
	if (@exists = 0)
	BEGIN
	PRINT '		File ' + @BackupFile + ' Does bot Exist'
	PRINT '		Database cannot be restored'
	PRINT '		Enter the valid Backup File'
	RETURN
	END

    	-- Cursor for all the spids running against this database
	DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
	SELECT spid
	FROM master.dbo.sysprocesses
	WHERE dbid = (	SELECT dbid FROM master.dbo.sysdatabases
	  		WHERE name = @DatabaseName)
	  	
	DECLARE @SysProcId smallint
	
	-- Opens the Cursor
	OPEN SysProc
	
	-- Fetch the Process ID into the cursor
 	FETCH NEXT FROM SysProc INTO @SysProcId
 	
	DECLARE @KillStatement char(30)
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))
		
		-- Kills the processes running against the database
		EXEC (@KillStatement)
		
		FETCH NEXT FROM SysProc INTO @SysProcId
	END
	
	WAITFOR DELAY '000:00:01'

	DECLARE @strSql varchar(2000)

	SET @strSql = 'RESTORE DATABASE '
	SET @strSql = @strSql + QUOTENAME(@DatabaseName)  
	SET @strSql = @strSql + 'FROM  DISK = N'+ '''' + @BackupFile + '''' 
	SET @strSql = @strSql + ' WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  RECOVERY ,  REPLACE'

	--PRINT @strSql
	
	-- Restore the Database
	EXEC (@strSql)

SET NOCOUNT OFF	
END
GO

Rate

Share

Share

Rate