sqlserver 差异备份,如何批量还原?

发布网友发布时间：2022-05-03 00:40

共2个回答

热心网友时间：2022-05-03 21:18

批量还原代码如下：

Use master
GO
/*=================Usp_RestoreMuiteDataBaseFromPath========================
=====Restore Mutite DataBase File From a Path ======
=====Ken.Guo ======
=====2010.9.10 ======
=====Version: 2005 & 2008 SQL Server ======
=====EXEC Usp_RestoreMuiteDataBaseFromPath 'D:\databack','',0 ======
=========================================================================
*/
CREATE PROC Usp_RestoreMuiteDataBaseFromPath
( @DatabBaseBakPath nvarchar(400)
,@RestoreDataPath nvarchar(400)='' --RESTORE DATABASE PATH
,@IsRun smallint=0 -- 0 PRINT 1 run
)
AS
BEGIN
set nocount on
DECLARE @BackUpFileName nvarchar(200)
,@DbName nvarchar(200)
,@errorinfo nvarchar(400)

IF not exists(SELECT 1
FROM master.sys.proceres WITH(NOLOCK)
WHERE
name=N'Usp_RestoreDataBaseFormPath'

)
begin
Raiserror 50001 N'找不到存储过程SP_RestoreDataBaseFormPath '
Goto ExitFLag
end

--add path \
if (@DatabBaseBakPath is not null) and len(@DatabBaseBakPath)>1
and (right(@DatabBaseBakPath,1)<>'\')
set @DatabBaseBakPath=@DatabBaseBakPath+'\'

--Check Restore Path and Size >1000M
DECLARE @checkdrive int
SET @checkdrive=1
EXEC master.dbo.Usp_Check_DriveExists @RestoreDataPath,@checkdrive OUTPUT

IF(@checkdrive<>1)
Goto ExitFLag

DECLARE @Dir TABLE
(
BackDBFileName nvarchar(100)
,DEPTH int
,[File] int
)

INSERT INTO @Dir EXEC xp_dirtree @DatabBaseBakPath
,1
,1

DELETE FROM @Dir
WHERE charindex('.bak',BackDBFileName)=0

if not exists (select top 1 1 from @Dir)
begin
Raiserror 50001 N'在提供的路径下没有找到合符要求的备份文件'
Goto ExitFLag
end

declare db_file Cursor Local Static Read_Only Forward_Only
for
select BackDBFileName from @Dir

Open db_file
Fetch Next from db_file into @BackUpFileName
while @@FETCH_STATUS=0
begin
--Restore DataBase
set @BackUpFileName=@DatabBaseBakPath+@BackUpFileName
exec master.dbo.Usp_RestoreDataBaseFormPath @BackUpFileName,@RestoreDataPath,@IsRun
Fetch Next from db_file into @BackUpFileName
end
Close db_file
deallocate db_file

ExitFLag:
set nocount off
end

热心网友时间：2022-05-03 22:36