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