通常，迁移服务器后，往往需要还原SQLSERVER的备份文件，如果BAK文件一多，恢复起来就比较费时间，以下可以通过SQL脚本的方法，进行批量还原SQLSERVER的备份文件，注意：需要指定 备份文件所在文件夹路径，还有 数据库安装路径中的数据文件夹路径，
1.首先把后缀为bak的备份文件放到指定盘目录上，例如“D:\db\”文件夹
2.找到数据库的安装路径下的数据库目录文件，例如“D:\Software\SQL2014\DB\MSSQL12.MSSQLSERVER\MSSQL\DATA\”
3.修改脚本里对应的目录配置，执行SQL脚本，具体脚本如下
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