--- Erstellt automatisch die Restore Kommandos um Restore für alle Dateien aus einem
--- Verzeichnis zu erstellen.
--- bak und sav werden berücksichtigt
DECLARE @SourceDirBackupFiles nvarchar(200)='F:\Daten\SQL\Backup\Usr-DB\'
Declare @Whatif bit = 0 ---1 Kommandos ausgeben ---0 Kommandos direkt ausführen
Declare @Single bit = 0
Declare @DestDirDbFiles nvarchar(200)=null
Declare @DestDirLogFiles nvarchar(200) =null
declare @rc int
Declare @datadir nvarchar(4000) = NULL
if @DestDirDbFiles is null
SET @DestDirDbFiles= cast (SERVERPROPERTY ('InstanceDefaultDataPath') as varchar(255)) --- nur >=2016!
IF @DestDirLogFiles is null
SET @DestDirLogFiles=cast (SERVERPROPERTY ('InstanceDefaultLogPath') as varchar(255)) --- nur >=2016!
SET NOCOUNT ON
DECLARE @crlf char(2) = CHAR(13) + CHAR(10)
CREATE TABLE #Files(fname varchar(200),depth int, file_ int)
INSERT #Files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
SELECT fname FROM #Files
Where ( fname Like ('%.sav') or fname Like ('%.bak') )
CREATE TABLE #HeaderResult(
BackupName nvarchar(128)
,BackupDescription nvarchar(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName nvarchar(128)
,ServerName nvarchar(128)
,DatabaseName nvarchar(128)
,DatabaseVersion bigint
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,[CodePage] smallint
,UnicodeLocaleId bigint
,UnicodeComparisonStyle bigint
,CompatibilityLevel tinyint
,SoftwareVendorId bigint
,SoftwareVersionMajor bigint
,SoftwareVersionMinor bigint
,SoftwareVersionBuild bigint
,MachineName nvarchar(128)
,Flags bigint
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bigint
,IsSnapshot bigint
,IsReadOnly bigint
,IsSingleUser bigint
,HasBackupChecksums bigint
,IsDamaged bigint
,BegibsLogChain bigint
,HasIncompleteMetaData bigint
,IsForceOffline bigint
,IsCopyOnly bigint
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel nvarchar(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize bigint
,Containment bigint
,KeyAlgorithm nvarchar(32)
,EncryptorThumbprint varbinary(20)
,EncryptorType nvarchar(23)
)
CREATE TABLE #datenbankdateien(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,[Type] char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint
,SourceBlockSize bigint
,FilegroupId bigint
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly bigint
,IsPresent int
,TDEThumbprint uniqueidentifier
,SnapshotUrl nvarchar(360)
)
DECLARE @FileName varchar(200)
DECLARE @dirfile varchar(300)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @type char(1)
DECLARE @DbName sysname
DECLARE @sql nvarchar(1000)
DECLARE files CURSOR FOR
SELECT fname FROM #Files
Where ( fname Like ('%.sav') or fname Like ('%.bak') )
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #datenbankdateien
OPEN files
FETCH NEXT FROM files INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @FileName
TRUNCATE TABLE #HeaderResult
INSERT #HeaderResult
EXEC('RESTORE HEADERONLY
FROM DISK = ''' + @dirfile + '''')
SET @DbName = (SELECT DatabaseName FROM #HeaderResult)
if @Single=1
SET @sql = 'ALTER DATABASE [' + @DbName + '] set single_user with rollback immediate' + @crlf
else
SET @sql =''
SET @sql = @sql + 'RESTORE DATABASE [' + @DbName + '] ' + @crlf
+ 'FROM DISK = ''' + @dirfile + '''' + ' WITH REPLACE' + @crlf
+ ', STATS = 10 ' + @crlf
+ ', MOVE '
TRUNCATE TABLE #datenbankdateien
INSERT #datenbankdateien
EXEC('RESTORE FILELISTONLY
FROM DISK = ''' + @dirfile + '''')
OPEN dbfiles
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
IF @@FETCH_STATUS <> 0
BREAK
IF @type = 'D'
BEGIN
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @LogicalName + '.mdf''' + @crlf
SET @sql = @sql + ', MOVE '
END
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirLogFiles + @LogicalName + '.ldf''' + @crlf END
SET @sql = @sql + @crlf IF @Whatif=1 PRINT @sql ELSE EXEC(@sql) CLOSE dbfiles FETCH NEXT
FROM files INTO @FileName END CLOSE files DEALLOCATE dbfiles DEALLOCATE files
DROP TABLE #Files
DROP TABLE #HeaderResult
DROP TABLE #datenbankdateien
