/***** 
遍歷文件夾進行數據庫還原
*******/---需要開啟xp_cmdshell 如已經開啟 可以略過
/***** Step 1 開啟 xp_cmdshell 
Use Master
GO
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
*******/IF OBJECT_ID('tempdb..#files') IS NULL
BEGIN--DROP TABLE  #filesCREATE TABLE #files(dbname     VARCHAR(200) NULL,dbsql      VARCHAR(7000) NULL)
ENDDELETE #files IF OBJECT_ID('tempdb..#filelistinfo') IS NOT NULLDROP TABLE #filelistinfoCREATE TABLE #filelistinfo
(LogicalName              NVARCHAR(128) NULL,PhysicalName             NVARCHAR(260) NULL,TYPE                     CHAR(1) NULL,FileGroupName            NVARCHAR(128) NULL,FileSize                 BIGINT NULL,MAXSIZE                  BIGINT NULL,FileId                   BIGINT,CreateLSN                NUMERIC(25, 0),DropLSN                  NUMERIC(25, 0) NULL,UniqueID                 UNIQUEIDENTIFIER,ReadOnlyLSN              NUMERIC(25, 0) NULL,ReadWriteLSN             NUMERIC(25, 0) NULL,BackupSizeInBytes        BIGINT,SourceBlockSize          INT,FileGroupID              INT,LogGroupGUID             UNIQUEIDENTIFIER NULL,DifferentialBaseLSN      NUMERIC(25, 0) NULL,DifferentialBaseGUID     UNIQUEIDENTIFIER,IsReadOnly               BIT,IsPresent                BIT,TDEThumbprint            BIT
)DECLARE @path VARCHAR(500)
DECLARE @pathData VARCHAR(500)
DECLARE @sql VARCHAR(8000)
DECLARE @bakName VARCHAR(500)
DECLARE @LogicalNameDat VARCHAR(500)
DECLARE @LogicalNameLog VARCHAR(500)
DECLARE @tempCommand VARCHAR(800) SET @path = 'E:\DataBak'    ---指定要處理的文件夾
SET @sql = 'dir ' + @path + ' /b'
SET @pathData = 'D:\SqlDataBase' ----數據庫還原到的目錄
SET @bakName = ''  -----統一的備份名稱（不要加.bak），比如固定日期或者可變名，根據自己的路徑規則定--獲取文件名稱，存放在#files
INSERT #files(dbname)
EXEC MASTER..xp_cmdshell @sql--從#files表遍歷處理據庫數信息，根據備份文件獲取數據邏輯名稱DECLARE @dbname VARCHAR(50)
DECLARE curs CURSOR 
FOR--定義游標cursSELECT dbnameFROM   #files               OPEN curs                       FETCH NEXT FROM curs INTO @dbname  WHILE @@fetch_status = 0 
BEGINSET @bakName = @dbname  --根據自己的路徑規則來定--根據自己路徑規則拼接數據庫備份文件路徑，并執行RESTORE FILELISTONLYSET @tempCommand = 'restore filelistonly from disk=''' + @path + '\' +@dbname + '\' + @bakName + '.bak'''PRINT @tempCommand--將得到數據庫的數據存入臨時表#filelistinfoINSERT INTO #filelistinfoEXEC (@tempCommand)SELECT @LogicalNameDat = LogicalNameFROM   #filelistinfoWHERE [TYPE]= 'D'  --數據庫文件SELECT @LogicalNameLog = LogicalNameFROM   #filelistinfoWHERE [TYPE]= 'L' --日志文件--拼接恢復數據庫語句，并更新對應記錄到#filesUPDATE #filesSET    dbsql = 'RESTORE DATABASE ' + dbname + ' FROM DISK = ''' + @path + '\' + dbname + '\' + @bakName +'.bak''' + ' WITH MOVE ''' + @LogicalNameDat + ''' TO ''' + @pathData + '\' +dbname+ '.mdf'','+ ' MOVE ''' + @LogicalNameLog + ''' TO ''' + @pathData + '\' +dbname +'_log.ldf'''WHERE  dbname = @dbname--清除#filelistinfo臨時表，繼續處理下一條記錄DELETE FROM   #filelistinfoFETCH NEXT FROM curs INTO @dbname  
ENDCLOSE curs                   DEALLOCATE cursSELECT *
FROM   #files--遍歷#files表 執行恢復
DECLARE cur CURSOR STATIC LOCAL 
FORSELECT dbsqlFROM   #filesOPEN curWHILE 1 = 1
BEGINFETCH cur INTO @sqlIF @@fetch_status <> 0BREAKEXEC (@sql)
ENDDEALLOCATE curDROP TABLE  #files

?

操作原理：

遍歷備份文件，將所有要還原的數據庫名稱存入#files

通過執行“RESTORE FILELISTONLY”，將結果存入##filelistinfo，從記錄中得到備份數據庫的庫文件及日志文件的邏輯文件名，拼接“RESTORE DATABASE”，并對應更新存入#files表

遍歷#files表，執行恢復語句

?

本例?備份文件存儲路徑參考：