🐇 🐇 Protect your #sqlserver database with backup and control log volume

full-backup-and-log-control.sql

-- ============================================================================
-- 1. Limitamos o tamanho do log a 200MB com variação de 50MB;
-- alter log size of database
USE [yourDatabase];
GO
ALTER DATABASE [yourDatabase]
  MODIFY FILE
  (NAME = yourDatabase_log,SIZE = 200MB, FILEGROWTH = 50MB);
GO

-- ============================================================================
-- 2. Cortamos o log se for maior que 200MB;
USE [yourDatabase];
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure database file wrap-around last transactions
GO
-- 200 MB
DBCC SHRINKFILE(yourDatabase_log, 200);

-- ============================================================================
-- 3. Criamos a rotina 1
-- Routine 1 | Step 1
-- Set FULL recovery mode
ALTER DATABASE yourDatabase SET RECOVERY FULL;

-- Routine 1 | Step 2
-- Do your first full database backup
-- reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql#backing_up_db
BACKUP DATABASE yourDatabase
  TO DISK = N'C:\your\backup\yourDatabase.bak'
  WITH FORMAT;
GO

-- ============================================================================
-- 4. Criamos a rotina 3
-- Routine 2 | Unique step
-- Do your differential database backup
-- reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql#backing_up_db
BACKUP DATABASE yourDatabase
  TO DISK = N'C:\your\backup\yourDatabase.bak'
  WITH DIFFERENTIAL;
GO

-- ============================================================================
-- 5. Criamos a rotina 3
-- Routine 3 | Unique step
-- Do your first log backup with date into file name
-- string @path represent file name with date
DECLARE @path NVARCHAR(255) = N'C:\your\backup\yourDatabase'
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';
 -- backup command
BACKUP LOG yourDatabase TO DISK = @path WITH INIT, COMPRESSION;