SQL Backup

Automatizzare i backup del database con SQL Agent

• Esegue il backup giornaliero di un database sql in modo automatico,
• Salva il backup in una cartella specifica con il nome del database e la data di esecuzione.

Step 1 - SQL Server Agent

Abilitare SQL Server Agent

$ sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Riavviare SQL Server

$ sudo systemctl restart mssql-server

Step 2 - Cartella di destinazione

La cartella predefinita del comando di backup è \var\opt\mssql\data

Creare la cartella di destinazione

# md \var\opt\mssql\backup

Assegnare il proprietario mssql

# chown -R mssql:mssql \var\opt\mssql\backup

Step 3 - Storage Procedure

StorageProcedure che esegue il backup del database aggiungendo al nome del file la data di esecuzione.

USE [DB_Nome]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NomeBackup] 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @BackupFileName varchar(100)
    SELECT @BackupFileName = '/var/opt/mssql/backup/DB_Nome_' + CONVERT (VarChar, GetDate(), 105) + '.bak'
    BACKUP DATABASE DB_Nome TO DISK = @BackupFilename WITH NOFORMAT, NOINIT, NAME='DB_Nome-full', SKIP, NOREWIND, NOUNLOAD, STATS = 1
END

Step 4 - SQL-JOBS

Crea un sql-job per eseguire un backup giornaliero del database

sp_add_job per creare un nuovo job

USE msdb ;
GO
EXEC dbo.sp_add_job
   @job_name = N'NomeBackupJob' ;
GO

sp_add_jobstep crea un jobstep che esegue il backup del database tramite una stored procedure

USE msdb ;
GO
EXEC sp_add_jobstep  
    @job_name = N'NomeBackupJob',  
    @step_name = N'NomeBackupJobStep',  
    @subsystem = N'TSQL',  
    @command = N'EXEC DB_Nome.dbo.BackupNome',   
    @retry_attempts = 5,  
    @retry_interval = 5 ;  
GO  

sp_add_schedule crea uno schedule per impostare la frequenza giornaliera e l'ora del backup alle ore 23:55.

USE msdb ;
GO
EXEC dbo.sp_add_schedule  
    @schedule_name = N'NomeBackupSchedule',  
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_date = 20230817,
    @active_start_time = 235500 ;  
GO  

sp_attach_schedule assegna lo schedule al job

USE msdb ;
GO
EXEC sp_attach_schedule  
   @job_name = N'NomeBackupJob',  
   @schedule_name = N'NomeBackupSchedule';  
GO

sp_add_jobserver assegna il job al server

USE msdb ;
GO
EXEC dbo.sp_add_jobserver  
    @job_name = N'NomeBackupJob';  
GO

sp_start_job avvia il job

EXEC dbo.sp_start_job N'NomeBackupJob' ;
GO