Änderungen
Die Seite wurde neu angelegt: „== Creating a Backup/Restore Script == Task : Using Backup Restore to migrate many databases to another Server with a different hard drive setup === T-SQL Sc…“
== 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 ===
<source lang="tsql">
-- Create Backup Restore Script for manual execution
DECLARE @BackupDest nvarchar(1000) = '\\someplace\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;' + 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;' + 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 + '.log'''
WHEN mf.type_desc = 'ROWS' and mf.file_id > 1 THEN @RestoreDest_DB + mf.name + '.ndf'''
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
</source>
Task : Using Backup Restore to migrate many databases to another Server with a different hard drive setup
=== T-SQL Script ===
<source lang="tsql">
-- Create Backup Restore Script for manual execution
DECLARE @BackupDest nvarchar(1000) = '\\someplace\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;' + 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;' + 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 + '.log'''
WHEN mf.type_desc = 'ROWS' and mf.file_id > 1 THEN @RestoreDest_DB + mf.name + '.ndf'''
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
</source>
