sqlserver 差异备份，如何批量还原？

Question

以前设置备份计划的时候，不小心弄成差异备份了，现在不能直接用最新的还原，报错是“...因为没有文件可用于前滚”。网上查了大概是备份是差异备份的原因，但网上差异备份还原的方法大多都只写了先还原一个最早的完整备份（这个我也有），再还原新的差异还原（但我有上百个，不可能手工一个一个操作）。

Answer 1

批量还原代码如下：

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.procedures 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

Answer 2

先还原最近完整备份,然后还原最新差异备份

追问

如何判断一对相似文件名里哪个是完整备份？