SQL SERVER批量还原数据库

DECLARE @SqlServerPath NVARCHAR(200) --数据库安装路径（数据文件夹）
DECLARE @BackUpPath NVARCHAR(200) --备份文件路径
DECLARE @BackUpFileName NVARCHAR(200) --备份文件名
DECLARE @DbName NVARCHAR(200) --db名称
DECLARE @LogicalDbName NVARCHAR(200) --逻辑db名称
DECLARE @LogicalLogName NVARCHAR(200) --逻辑log名称
DECLARE @BackUpFullFileName NVARCHAR(260) --备份文件全名
DECLARE @RESTORESQL NVARCHAR(2000) --备份数据库完整语句
SET @BackUpPath = 'D:\db\'; --备份文件路径
SET @SqlServerPath = 'D:\Software\SQL2014\DB\MSSQL12.MSSQLSERVER\MSSQL\DATA\'; --数据库安装路径（数据文件夹）
--add path \
if (@BackUpPath IS NOT NULL) AND LEN(@BackUpPath)>1 AND (RIGHT(@BackUpPath,1)<>'\')
BEGIN
 SET @BackUpPath=@BackUpPath+'\'
END
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp
CREATE TABLE #Temp (id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@BackUpPath,3)
INSERT #Temp EXEC master..xp_fileexist @fpath
INSERT #Temp EXEC master..xp_fileexist @BackUpPath
--如果指定盘符有误不存在，则返回错误提示：
IF EXISTS(SELECT 1 FROM #Temp WHERE id=1 AND c=0)
BEGIN
    PRINT CHAR(10) + N'备份文件路径的盘符不存在，请重新输入!'
	GOTO ExitFLag
END
--如果不存在指定的文件夹，则创建:
ELSE IF EXISTS(SELECT 1 FROM #Temp WHERE b=0 AND id=2)
BEGIN
   PRINT CHAR(10) + N'备份文件路径不存在，请重新输入!'
   GOTO ExitFLag
END
IF OBJECT_ID('tempdb..#Dir') IS NOT NULL
    DROP TABLE #Dir
CREATE TABLE #Dir  
(  
     BackDBFileName NVARCHAR(100) ,DEPTH INT ,[File] INT  
)
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
)
 
INSERT INTO #Dir EXEC master..xp_dirtree @BackUpPath,1,1
 
DELETE FROM #Dir WHERE CHARINDEX('.bak', BackDBFileName)=0
IF NOT EXISTS (SELECT TOP 1 1 FROM #Dir)
BEGIN
   PRINT CHAR(10) + N'在提供的路径下没有找到合符要求的备份文件'    
   Goto ExitFLag
 END
 
 PRINT N'开始还原所有数据库'
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 @BackUpFullFileName=@BackUpPath+@BackUpFileName
  SET @DbName = REPLACE(@BackUpFileName,'.bak','')
  DELETE FROM #FileListInfo
  INSERT INTO #FileListInfo
    EXEC ('RESTORE FILELISTONLY FROM DISK=''' + @BackUpFullFileName + '''')
	SET @LogicalDbName=''
	SELECT @LogicalDbName = LogicalName FROM   #FileListInfo WHERE [TYPE]= 'D'  --数据库文件  
	SET @LogicalLogName=''
    SELECT @LogicalLogName = LogicalName FROM   #FileListInfo WHERE [TYPE]= 'L' --日志文件
	IF (@LogicalDbName IS NULL OR @LogicalDbName = '')  
   SET @LogicalDbName = @DbName
    IF (@LogicalLogName IS NULL OR @LogicalLogName = '')  
   SET @LogicalLogName = @DbName + '_log'
  SET @RESTORESQL = '  
  IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name]='''+@DbName+''')
  BEGIN
  EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+@DbName+'''  
  USE [master]  
  DROP DATABASE ['+@DbName +']  
  END
  RESTORE DATABASE '+@DbName+' FROM DISK = '''+@BackUpFullFileName+'''  
  WITH MOVE '''+@LogicalDbName+''' TO '''+@SqlServerPath+@DbName+'.mdf''
 ,MOVE '''+@LogicalLogName+''' TO '''+@SqlServerPath+@DbName+'_log.ldf'' '
   PRINT @RESTORESQL
   EXECUTE  sp_executesql @RESTORESQL
  FETCH NEXT FROM db_file INTO @BackUpFileName
 END
CLOSE db_file
DEALLOCATE db_file
PRINT N'
所有数据库还原完成'
ExitFLag:
----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
  DROP TABLE #Temp  
IF OBJECT_ID('tempdb..#Dir') IS NOT NULL
    DROP TABLE #Dir
IF OBJECT_ID('tempdb..#FileListInfo') IS NOT NULL
    DROP TABLE #FileListInfo