结束指定数据库的所有用户会话,强制数据库还原:USE masterGO IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[spForceRestoreDB]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[spForceRestoreDB] GOSET 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 Res : 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
IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spForceRestoreDB]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spForceRestoreDB]
GOSET 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 Res : 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