强制还原已存在的数据库

相关链接：

• SQL数据恢复问题深入剖析强制还原已存在的数据库

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

/*
强制还原已存在的数据库，基本原理是Kill掉数据库的所有会话
原贴在http://www.sqlservercentral.com/scripts/contributions/827.asp
在此基础上添加了with move选项

调用；
use master
go
exec master..spForceRestoreDB 'test'
,'c:/test.bak'
,'c:/db/test_data.mdf'
,'c:/db/test_log.mdf'

vivianfdlpw 2005.9 引用请保留此信息
*/
CREATE PROCEDURE spForceRestoreDB
@DatabaseName varchar(50), --要恢复的数据库名
@BackupFile varchar(255), --备份文件路径
@NewDataFilePath varchar(255), --新物理文件位置
@NewLogFilePath 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'

create table #
(LogicalName varchar(255),
PhysicalName varchar(255),
Type varchar(20),
FileGroupName varchar(255),
Size varchar(20),
MaxSize varchar(20) )
declare @cmd varchar(200)
,@DataLogicName varchar(20)
,@logLogicName varchar(20)
select @cmd = 'RESTORE FILELISTONLY FROM disk = '''+ @BackupFile + ''''
insert # exec(@cmd)

select @DataLogicName=LogicalName from # where Type='D'
select @logLogicName=LogicalName from # where Type='L'
drop table #

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'
SET @strSql = @strSql + ',Move '''+@DataLogicName+''' to '''+@NewDataFilePath+''''
SET @strSql = @strSql + ',Move '''+@logLogicName+''' to '''+@NewLogFilePath+''''

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

SET NOCOUNT OFF
END
GO

<script language="javascript" type="text/javascript"> document.title="强制还原已存在的数据库 - "+document.title </script>