-- Create Backup Restore Script for manual execution
DECLARE @BackupDest nvarchar(1000) = '\\someplacekhvmsqlbkp01\Temp\';
DECLARE @RestoreDest_DB nvarchar(1000) = 'L:\DB_Data\';
DECLARE @RestoreDest_Log nvarchar(1000) = 'L:\DB_Log\';
DECLARE @SQLSkript nvarchar(max) = '';
select @SQLSkript = (@SQLSkript + 'BACKUP DATABASE [' + name + '] TO DISK = N'''+@BackupDest+name+'.bak'' WITH COMPRESSION, COPY_ONLY;' + CHAR(10)) from sys.databases where database_id > 4;
select @SQLSkript
SET @SQLSkript = '';
select @SQLSkript = (@SQLSkript + 'RESTORE DATABASE [' + db.name + '] FROM DISK = N'''+@BackupDest+db.name+'.bak'' WITH FILE = 1 ' + mf.name + ' ,RECOVERY, REPLACE;' + CHAR(10))
from sys.databases db
cross apply (select ', MOVE N''' + mf.name + ''' TO N''' +
WHEN mf.type_desc = 'LOG' THEN @RestoreDest_Log + mf.name + '.log'''
WHEN mf.type_desc = 'ROWS' and mf.file_id > 1 THEN @RestoreDest_DB + mf.name + '.ndf'''
WHEN mf.type_desc = 'FILESTREAM' THEN @RestoreDest_DB + mf.name + '\'''
ELSE '!!!!!!!!!!!!!!MISSING TYPE!!!!!!!!!!!!! : ' + mf.type_desc + ' : !!!!!!!!!'
END as [text()]
from sys.master_files mf where db.database_id = mf.database_id order by mf.file_id FOR XML PATH('')) mf(name)
Änderungen – Software Entwicklung Projekte
Hauptmenü öffnen

Änderungen