MSSQL Create Backup Restore Script

Aus Software Entwicklung Projekte
Wechseln zu: Navigation, Suche

Creating a Backup/Restore Script

Task : Using Backup Restore to migrate many databases to another Server with a different hard drive setup

T-SQL Script

-- Create Backup Restore Script for manual execution
 
DECLARE @BackupDest nvarchar(1000) = '\\khvmsqlbkp01\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''' +  
					CASE 
						WHEN mf.type_desc = 'ROWS' and mf.file_id = 1 THEN @RestoreDest_DB + mf.name + '.mdf''' 
						WHEN mf.type_desc = 'LOG' THEN @RestoreDest_Log + mf.name + '.ldf''' 
						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)		
	where db.database_id > 4 
	order by db.database_id;
select @SQLSkript
 
-- After restore change database version to 2014
SET @SQLSkript = '';
select @SQLSkript = (@SQLSkript + 'ALTER DATABASE [' + name + '] SET COMPATIBILITY_LEVEL = 120' + CHAR(10)) from sys.databases where database_id > 4;
select @SQLSkript