T-SQL backup job scriptal always on sistemde, shrink
Definition Description
Bir job içine tepler ahlinde sırası ile aşağıdaki scriptleri yazabilirsin.
STEP 1- TRN dosyalarını sil
if (select ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = 'SQLKNAG1' and ars.is_local = 1) = 'SECONDARY'
BEGIN
declare @gunTar datetime set @gunTar = getdate()-1
END ELSE BEGIN PRINT 'Primary replika değil, job çalışmadı' END
STEP 2- TLoBackup Al
declare @gunTar nvarchar(20), @path nvarchar(100) set @gunTar = format(getdate(), 'yyyyMMdd_HHmmss')
if (select ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = 'SQLKNAG1' and ars.is_local = 1) = 'SECONDARY'
BEGIN set @path = CONCAT( N'R:\Backup\ArchiveDB_', @gunTar, '.trn') BACKUP LOG [ArchiveDB] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'ArchiveDB-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\AtlantisDB_', @gunTar, '.trn') BACKUP LOG [AtlantisDB] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'AtlantisDB-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\DBA_', @gunTar, '.trn') BACKUP LOG [DBA] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'DBA-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\DPAudit_', @gunTar, '.trn') BACKUP LOG [DPAudit] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'DPAudit-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\ElasticSearch_', @gunTar, '.trn') BACKUP LOG [ElasticSearch] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'ElasticSearch-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\ISD_', @gunTar, '.trn') BACKUP LOG [ISD] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'ISD-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\KNetLogs_', @gunTar, '.trn') BACKUP LOG [KNetLogs] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'KNetLogs-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\KNetStats_', @gunTar, '.trn') BACKUP LOG [KNetStats] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'KNetStats-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\YetkiTempDB_', @gunTar, '.trn') BACKUP LOG [YetkiTempDB] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'YetkiTempDB-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
set @path = CONCAT( N'R:\Backup\ElasticTransaction_', @gunTar, '.trn') BACKUP LOG [ElasticTransaction] TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'YetkiTempDB-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 5
END ELSE BEGIN PRINT 'Primary replika değil, job çalışmadı' END
STEP 3- SHRINK TLog Files DIYELIMKI ASAGIDAKI GIBI ATLANTISDB log DOSYASINI 300000 GB limitledin, shrink aşağıdaki şekilde olur.
declare @gunTar nvarchar(20), @path nvarchar(100) set @gunTar = format(getdate(), 'yyyyMMdd_HHmmss')
if (select ars.role_desc from sys.dm_hadr_availability_replica_states ars inner join sys.availability_groups ag on ars.group_id = ag.group_id where ag.name = 'SQLKNAG1' and ars.is_local = 1) = 'PRIMARY'
