遍历文件夹还原数据库SQL语句
/*****
遍历文件夹进行数据库还原
*******/
---需要开启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 #files
CREATE TABLE #files
(
dbname VARCHAR(200) NULL,
dbsql VARCHAR(7000) NULL
)
END
DELETE #files
IF OBJECT_ID('tempdb..#filelistinfo') IS NOT NULL
DROP TABLE #filelistinfo
CREATE 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
--定义游标curs
SELECT dbname
FROM #files
OPEN curs
FETCH NEXT FROM curs INTO @dbname
WHILE @@fetch_status = 0
BEGIN
SET @bakName = @dbname --根据自己的路径规则来定
--根据自己路径规则拼接数据库备份文件路径,并执行RESTORE FILELISTONLY
SET @tempCommand = 'restore filelistonly from disk=''' + @path + '\' +
@dbname + '\' + @bakName + '.bak'''
PRINT @tempCommand
--将得到数据库的数据存入临时表#filelistinfo
INSERT INTO #filelistinfo
EXEC (@tempCommand)
SELECT @LogicalNameDat = LogicalName
FROM #filelistinfo
WHERE [TYPE]= 'D' --数据库文件
SELECT @LogicalNameLog = LogicalName
FROM #filelistinfo
WHERE [TYPE]= 'L' --日志文件
--拼接恢复数据库语句,并更新对应记录到#files
UPDATE #files
SET 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 #filelistinfo
FETCH NEXT FROM curs INTO @dbname
END
CLOSE curs
DEALLOCATE curs
SELECT *
FROM #files
--遍历#files表 执行恢复
DECLARE cur CURSOR STATIC LOCAL
FOR
SELECT dbsql
FROM #files
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @sql
IF @@fetch_status <> 0
BREAK
EXEC (@sql)
END
DEALLOCATE cur
DROP TABLE #files
操作原理:
遍历备份文件,将所有要还原的数据库名称存入#files
通过执行“RESTORE FILELISTONLY”,将结果存入##filelistinfo,从记录中得到备份数据库的库文件及日志文件的逻辑文件名,拼接“RESTORE DATABASE”,并对应更新存入#files表
遍历#files表,执行恢复语句
本例 备份文件存储路径参考: