SQL SERVER批量还原数据库语句

当需要还原多个数据库时，一个一个还原会比较麻烦，通过语句的方式批量还原数据库解放双手，完美执行。

执行前需要赋值的变量：

@BackupFilePath：备份所在文件夹

@DataPath：新库所在文件夹

@DataBackup：通过临时表的方式指定需要还原的备份库

完整SQL语句：

--备份所在文件夹
DECLARE @BackupFilePath NVARCHAR(MAX) = N'E:\MSSQL\Backup\'
--新库所在文件夹
DECLARE @DataPath NVARCHAR(MAX) = N'E:\MSSQL\DATA\'

DECLARE @DataBackup TABLE (NewDataName NVARCHAR(100), BackUpName NVARCHAR(100));

--自定语句:新的库名,备份文件名
INSERT INTO @DataBackup 
SELECT N'NewTest1', N'test'
UNION SELECT N'NewTest2', N'test'


DECLARE @DatabaseName NVARCHAR(MAX)--数据库名
DECLARE @BackupName NVARCHAR(MAX)--备份名
DECLARE @DataLogicalName NVARCHAR(MAX)--数据逻辑名
DECLARE @LogLogicalName NVARCHAR(MAX)--日志逻辑名

--备份信息临时表
DECLARE @FileList TABLE (
    LogicalName NVARCHAR(128),
    PhysicalName NVARCHAR(260),
    [Type] CHAR(1),
    FileGroupName NVARCHAR(128),
    Size BIGINT,
    MaxSize BIGINT,
    FileID BIGINT,
    CreateLSN NUMERIC,
    DropLSN NUMERIC,
    UniqueID UNIQUEIDENTIFIER,
    ReadOnlyLSN NUMERIC,
    ReadWriteLSN NUMERIC,
    BackupSizeInBytes BIGINT,
    SourceBlockSize INT,
    FileGroupID INT,
    LogGroupGUID UNIQUEIDENTIFIER,
    DifferentialBaseLSN NUMERIC,
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    IsReadOnly BIT,
    IsPresent BIT,
    TDEThumbprint VARBINARY(32),
    SnapshotUrl NVARCHAR(32)
);

--定义游标
DECLARE db_cursor CURSOR FOR
select * from @DataBackup

--打开游标
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName, @BackupName

WHILE @@FETCH_STATUS = 0
BEGIN

	--清空备份信息表
	DELETE FROM @FileList;
	--插入备份信息表
	INSERT INTO @FileList
	EXEC('RESTORE FILELISTONLY FROM DISK = '''+@BackupFilePath + @BackupName+'.bak''');
	--读取逻辑名
	SELECT @DataLogicalName = LogicalName FROM @FileList where Type = 'D';
	SELECT @LogLogicalName = LogicalName FROM @FileList where Type = 'L';
	
	--数据库是否存在
	DECLARE @DataBaseIsExist int = 0 

	IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
	BEGIN
		set @DataBaseIsExist = 1
	END

	--SQL变量
    DECLARE @Sql NVARCHAR(MAX)
    DECLARE @Sql1 NVARCHAR(MAX)
    DECLARE @Sql2 NVARCHAR(MAX)
	
	--数据库连接
	SET @Sql1 = 'ALTER DATABASE '+@DatabaseName+' SET OFFLINE WITH ROLLBACK IMMEDIATE'
    
	IF @DataBaseIsExist = 1
	BEGIN
		EXEC sp_executesql @Sql1
	END
	--还原数据库
	SET @Sql = 'RESTORE DATABASE ' + QUOTENAME(@DatabaseName) + 
               ' FROM DISK = ''' + @BackupFilePath + @BackupName + '.bak'' ' +
               ' WITH REPLACE, RECOVERY, ' +
               ' MOVE ''' + @DataLogicalName + ''' TO N''' + @DataPath + @DatabaseName+'.mdf'', ' +
               ' MOVE ''' + @LogLogicalName + ''' TO N''' + @DataPath + @DatabaseName+'_log.ldf'';'
    
	print @Sql
    EXEC sp_executesql @Sql
	--恢复数据库连接
	SET @Sql2 = 'ALTER DATABASE '+@DatabaseName+' SET ONLINE;'
    
	IF @DataBaseIsExist = 1
	BEGIN
		EXEC sp_executesql @Sql2
	END
    --指针
    FETCH NEXT FROM db_cursor INTO @DatabaseName, @BackupName
END
--关闭游标
CLOSE db_cursor
DEALLOCATE db_cursor