{"id":2353,"date":"2025-01-07T14:53:29","date_gmt":"2025-01-07T14:53:29","guid":{"rendered":"https:\/\/www.klokocka.info\/?p=2353"},"modified":"2025-01-15T11:42:56","modified_gmt":"2025-01-15T11:42:56","slug":"mssql-on-ubuntu-24-04-scheduled-backups","status":"publish","type":"post","link":"https:\/\/www.klokocka.info\/?p=2353","title":{"rendered":"MSSQL on Ubuntu 24.04 – Scheduled Backups"},"content":{"rendered":"\n

Sources:<\/p>\n\n\n\n

https:\/\/solutioncenter.apexsql.com\/how-to-delete-old-database-backup-files-automatically-in-sql-server\/<\/a><\/p>\n\n\n\n

https:\/\/www.mssqltips.com\/sqlservertip\/1070\/simple-script-to-backup-all-sql-server-databases<\/a><\/p>\n\n\n\n

Procedure for deleting old backup files<\/h2>\n\n\n\n
SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE OR ALTER  PROCEDURE [dbo].[usp_DeleteOldBackupFiles] \n    @path NVARCHAR(256) = '\/var\/opt\/mssql\/backup\/daily\/', --path of backups\n\t@extension NVARCHAR(10) = 'bak', -- extension of backup files\n\t@age_hrs INT = 10 --older than (hours). \n    --Use negative value for deleting everything\nAS\nBEGIN\n\tSET NOCOUNT ON;\n\n\tDECLARE @DeleteDate NVARCHAR(50)\n\tDECLARE @DeleteDateTime DATETIME\n\n\tSET @DeleteDateTime = DateAdd(hh, - @age_hrs, GetDate())\n\n        SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '\/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))\n\n\tEXECUTE master.dbo.xp_delete_file 0,\n\t\t@path,\n\t\t@extension,\n\t\t@DeleteDate,\n\t\t1\nEND\n\nGO\n<\/pre>\n\n\n\n
Procedure for backup<\/h2>\n\n\n\n
SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE OR ALTER  PROCEDURE [dbo].[usp_BackupAllUserDatabases] \n    @path NVARCHAR(256) = '\/var\/opt\/mssql\/backup\/daily\/', --path of backups\n    @extension NVARCHAR(10) = 'bak', -- extension of backup files\n    @custom_string NVARCHAR(10) = '' -- extension of backup files\nAS\nBEGIN\n\tSET NOCOUNT ON;\n\n    DECLARE @name NVARCHAR(256) -- database name  \n    DECLARE @fileName NVARCHAR(512) -- filename for backup  \n    DECLARE @fileDate NVARCHAR(40) -- used for file name\n \n    -- specify filename format\n    SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) \n \n    DECLARE db_cursor CURSOR READ_ONLY FOR  \n    SELECT name  FROM master.sys.databases \n    WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases\n    AND state = 0 -- database is online\n    AND is_in_standby = 0 -- database is not read only for log shipping\n \n    OPEN db_cursor   \n    FETCH NEXT FROM db_cursor INTO @name   \n \n    WHILE @@FETCH_STATUS = 0   \n        BEGIN   \n        SET @fileName = @path + @name + '_' + @custom_string + @fileDate + '.' +@extension   \n        BACKUP DATABASE @name TO DISK = @fileName  \n        WITH COMPRESSION\n        \n        FETCH NEXT FROM db_cursor INTO @name   \n    END\n    CLOSE db_cursor   \n    DEALLOCATE db_cursor\t\t\nEND\nGO\n<\/pre>\n\n\n\n
Using procedures<\/h2>\n\n\n\n
 # Daily backup - set the job in SQL server Management studio\n DECLARE @path NVARCHAR(256) = '\/var\/opt\/mssql\/backup\/daily\/'\n DECLARE @custom_string NVARCHAR(50) = 'daily_'\n DECLARE @extension NVARCHAR(10) = 'bak'\n DECLARE @age_hrs INT = 30 * 24 --delete files older than 30 days\n\n EXECUTE master.dbo.usp_DeleteOldBackupFiles @path,@extension,@age_hrs  \n EXECUTE master.dbo.usp_BackupAllUserDatabases @path, @extension, @custom_string  \n\n# Monthly backup - set the job in SQL server Management studio\n DECLARE @path NVARCHAR(256) = '\/var\/opt\/mssql\/backup\/monthly\/'\n DECLARE @custom_string NVARCHAR(50) = 'monthly_'\n DECLARE @extension NVARCHAR(10) = 'bak'\n DECLARE @age_hrs INT = 12 * 30 * 24  --delete files older than 12 month\n\n EXECUTE master.dbo.usp_DeleteOldBackupFiles @path,@extension,@age_hrs  \n EXECUTE master.dbo.usp_BackupAllUserDatabases @path, @extension, @custom_string  \n\n<\/pre>\n\n\n\n
Copy files from docker to host system<\/h2>\n\n\n\n
# Copy files from docker to host system\nsudo docker cp sql1:\/var\/opt\/mssql\/backup\/ \/home\/media\/Ostatni\/mssql\/\n\n#Configure crontab manualy or Use Webmin or Zeit\n#run every day at 06:15\n#15 6 * * * docker cp sql1:\/var\/opt\/mssql\/backup\/ \/home\/webserver\/mssql\/\n#20 6 * * * chmod -R 777 \/home\/webserver\/mssql\/\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 Procedure for backup Using procedures Copy files from docker to host system<\/p>\n","protected":false},"author":1,"featured_media":2363,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"kt_blocks_editor_width":"","pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-2353","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/posts\/2353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2353"}],"version-history":[{"count":8,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/posts\/2353\/revisions"}],"predecessor-version":[{"id":2398,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/posts\/2353\/revisions\/2398"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=\/wp\/v2\/media\/2363"}],"wp:attachment":[{"href":"https:\/\/www.klokocka.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.klokocka.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}