现在我的一个实例下有上百个数据库。我想一次全部备份和还原,
备份我倒是做到了。维护计划中可以设置多个数据库一起备份。
但是,恢复时却出了难题:没有一次恢复的功能啊?只能手动一个个的恢复。
如何办啊?请问:1。不写脚本能否完成自动恢复所有的数据库?
2。实在没办法,请高手给个自动恢复所有数据库的脚本。备份名称是维护计划中产生的。无须人工干预。
备份我倒是做到了。维护计划中可以设置多个数据库一起备份。
但是,恢复时却出了难题:没有一次恢复的功能啊?只能手动一个个的恢复。
如何办啊?请问:1。不写脚本能否完成自动恢复所有的数据库?
2。实在没办法,请高手给个自动恢复所有数据库的脚本。备份名称是维护计划中产生的。无须人工干预。
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 intIF NOT EXISTS( select name from master.dbo.sysdatabases WHERE name = @DatabaseName)
BEGIN
PRINT 'Database ' + @DatabaseName + ' not found '
PRINT 'Enter valid Datbase name'
RETURN
ENDEXEC 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
ENDWAITFOR 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
我是想根据多个备份文件,一次恢复所有数据库。
恢复:@BackupFile 下的所有备份文件。
zlp321002:@DatabaseName是哪个数据库的名字啊?:@BackupFile是文件路径名?