MSSQL on Ubuntu 24.04 – Scheduled Backups

Sources:

https://solutioncenter.apexsql.com/how-to-delete-old-database-backup-files-automatically-in-sql-server/

https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases

Procedure for deleting old backup files

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER  PROCEDURE [dbo].[usp_DeleteOldBackupFiles] 
    @path NVARCHAR(256) = '/var/opt/mssql/backup/daily/', --path of backups
	@extension NVARCHAR(10) = 'bak', -- extension of backup files
	@age_hrs INT = 10 --older than (hours). 
    --Use negative value for deleting everything
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DeleteDate NVARCHAR(50)
	DECLARE @DeleteDateTime DATETIME

	SET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())

        SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

	EXECUTE master.dbo.xp_delete_file 0,
		@path,
		@extension,
		@DeleteDate,
		1
END

GO

Procedure for backup

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER  PROCEDURE [dbo].[usp_BackupAllUserDatabases] 
    @path NVARCHAR(256) = '/var/opt/mssql/backup/daily/', --path of backups
    @extension NVARCHAR(10) = 'bak', -- extension of backup files
    @custom_string NVARCHAR(10) = '' -- extension of backup files
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @name NVARCHAR(256) -- database name  
    DECLARE @fileName NVARCHAR(512) -- filename for backup  
    DECLARE @fileDate NVARCHAR(40) -- used for file name
 
    -- specify filename format
    SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
    DECLARE db_cursor CURSOR READ_ONLY FOR  
    SELECT name  FROM master.sys.databases 
    WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
    AND state = 0 -- database is online
    AND is_in_standby = 0 -- database is not read only for log shipping
 
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name   
 
    WHILE @@FETCH_STATUS = 0   
        BEGIN   
        SET @fileName = @path + @name + '_' + @custom_string + @fileDate + '.' +@extension   
        BACKUP DATABASE @name TO DISK = @fileName  
        WITH COMPRESSION
        
        FETCH NEXT FROM db_cursor INTO @name   
    END
    CLOSE db_cursor   
    DEALLOCATE db_cursor		
END
GO

Using procedures

 # Daily backup - set the job in SQL server Management studio
 DECLARE @path NVARCHAR(256) = '/var/opt/mssql/backup/daily/'
 DECLARE @custom_string NVARCHAR(50) = 'daily_'
 DECLARE @extension NVARCHAR(10) = 'bak'
 DECLARE @age_hrs INT = 30 * 24 --delete files older than 30 days

 EXECUTE master.dbo.usp_DeleteOldBackupFiles @path,@extension,@age_hrs  
 EXECUTE master.dbo.usp_BackupAllUserDatabases @path, @extension, @custom_string  

# Monthly backup - set the job in SQL server Management studio
 DECLARE @path NVARCHAR(256) = '/var/opt/mssql/backup/monthly/'
 DECLARE @custom_string NVARCHAR(50) = 'monthly_'
 DECLARE @extension NVARCHAR(10) = 'bak'
 DECLARE @age_hrs INT = 12 * 30 * 24  --delete files older than 12 month

 EXECUTE master.dbo.usp_DeleteOldBackupFiles @path,@extension,@age_hrs  
 EXECUTE master.dbo.usp_BackupAllUserDatabases @path, @extension, @custom_string

Copy files from docker to host system

# Copy files from docker to host system
sudo docker cp sql1:/var/opt/mssql/backup/ /home/media/Ostatni/mssql/

#Configure crontab manualy or Use Webmin or Zeit
#run every day at 06:15
#15 6 * * * docker cp sql1:/var/opt/mssql/backup/ /home/webserver/mssql/
#20 6 * * * chmod -R 777 /home/webserver/mssql/