SQLSERVER 2008批量还原数据库

USE Master
GO

DECLARE @BakPath nvarchar(200)
DECLARE @RestorePath nvarchar(200)
DECLARE @DataBaseName nvarchar(100)
DECLARE @DBName nvarchar(100)
DECLARE @SQL nvarchar(max)
 
 
SET @DBName='NSMC_HOTEL'   --还原数据库名
SET @BakPath='D:\Data\F4502'    --备份集路径
SET @RestorePath='D:\4502\NSMC_HOTEL_DB' --还原路径

 

DECLARE DB_CURSOR CURSOR FORWARD_ONLY FOR
  SELECT @DBName
 

CREATE TABLE #Temp(
  LogicalName nvarchar(100),
  PhysicalName nvarchar(100),
  Type nvarchar(100),
  FileGroupName nvarchar(100),
  Size nvarchar(100),
  MaxSize nvarchar(100),
  FileId nvarchar(100),
  CreateLSN nvarchar(100),
  DropLSN nvarchar(100),
  UniqueId nvarchar(100),
  ReadOnlyLSN nvarchar(100),
  ReadWriteLSN nvarchar(100),
  BackupSizeInBytes nvarchar(100),
  SourceBlockSize nvarchar(100),
  FileGroupId nvarchar(100),
  LogGroupGUID nvarchar(100),
  DifferentialBaseLSN nvarchar(100),
  DifferentialBaseGUID nvarchar(100),
  IsReadOnly nvarchar(100),
  IsPresent nvarchar(100),
  TDEThumbprint nvarchar(100))
 
 OPEN DB_CURSOR
 
 FETCH NEXT FROM DB_CURSOR
 INTO @DataBaseName

 WHILE @@FETCH_STATUS=0
 BEGIN
 PRINT @DataBaseName
 
 INSERT INTO #Temp
 EXEC ('RESTORE FILELISTONLY FROM DISK='''+@BakPath+'\'+@DataBaseName+'.bak''')

 SET @SQL=NULL

 SELECT
  @SQL=ISNULL(@SQL,'')+',
  MOVE '''+LogicalName+''' TO '''+@RestorePath+'\'+REVERSE(LEFT(REVERSE(PhysicalName),CHARINDEX('\',REVERSE(PhysicalName))-1)) +''''
 FROM #Temp

 SET @SQL='RESTORE DATABASE '+@DataBaseName+' FROM DISK='''+@BakPath+'\'+@DataBaseName+'.bak''
 WITH RECOVERY ,REPLACE'+@SQL

 --Print @SQL

 EXEC (@SQL)
 TRUNCATE TABLE #Temp

 FETCH NEXT FROM DB_CURSOR
 INTO @DataBaseName
 END

 CLOSE DB_CURSOR
 DEALLOCATE DB_CURSOR

 DROP TABLE #Temp