sql server存储器批量还原.bak数据库

sql server存储器批量还原.bak数据库

DECLARE
	@dicpath AS NVARCHAR(256),
	@datapath AS NVARCHAR(256);
set @dicpath = 'D:\MSSQL\Backup'
set @datapath = 'D:\MSSQL\Data'
truncate table [TempTable].[dbo].[TempTables]
INSERT [TempTable].[dbo].[TempTables] EXEC master..xp_dirtree @dicpath
,0
,1
-- 声明变量
DECLARE
    @subdirectory AS NVARCHAR(256),
    @subdirectoryTemp AS NVARCHAR(256),
    @bak AS NVARCHAR(256),--bak路径
    @gta AS NVARCHAR(256),--gta路径
    @log AS NVARCHAR(256),--log名称
    @ldf AS NVARCHAR(256),--ldf路径
    @Sql_tb as nvarchar(3000),
    @posStart as int,
    @posCur as int,
    @db_name AS NVARCHAR(256);--数据库名称
-- 声明游标
DECLARE C_Employees CURSOR FAST_FORWARD FOR
	select subdirectory 
	from [TempTable].[dbo].[TempTables]
    where depth = 1 and [file] = 1 and subdirectory like '%.bak';
    
OPEN C_Employees;
-- 取第一条记录
FETCH NEXT FROM C_Employees INTO @subdirectory;
set @posCur = 1
set @posStart = 1
WHILE @@FETCH_STATUS=0
BEGIN
    -- 操作
    set @subdirectoryTemp = @subdirectory
    SET @posCur = CHARINDEX('.',@subdirectoryTemp,@posStart)
    set @subdirectoryTemp = SUBSTRING(@subdirectoryTemp,@posStart ,@posCur-@posStart)
    set @db_name = @subdirectoryTemp
    set @bak = @dicpath + '\\' + @subdirectory
    set @gta = @datapath + '\\' + @subdirectoryTemp + '_1.gta'
    set @log = @subdirectoryTemp + '_log'
    set @ldf = @datapath + '\\' + @subdirectoryTemp + '_1.ldf'
    set @Sql_tb = 'RESTORE DATABASE [' + @db_name + '] FROM  DISK = N''' + @bak + ''' WITH  FILE = 1,  MOVE N''' + @db_name + ''' TO N''' + @gta + ''',  MOVE N''' + @log + ''' TO N''' + @ldf + ''',  NOUNLOAD,  STATS = 10'
    print @sql
    exec sp_executesql @sql
    FETCH NEXT FROM C_Employees INTO @subdirectory;
	
END
-- 关闭游标
CLOSE C_Employees;
-- 释放游标
DEALLOCATE C_Employees;