In this post, we are configuring automatic backups on a SQL Server 2017 instance on Linux.
We will use a script launched from Crontab. As simple as that.
Preparing a backup script.
The backup script is going to have a launcher called Launch_Backup.sh and the sql backup script called Backup.sql.
Preparing the launcher
This launcher is not really needed, you will see that it contains only the “sqlcmd” command with the needed parameters. But it is kind of handy to do it this way.
Create Launch_Backup.sh with the following content:
#!/bin/bash sqlcmd -S localhost -i Backup.sql -U BackupUser -P BackupPassword
As you can see, you will need credentials to connect to the instance. In our example BackupUser and BackupPassword.
You can create a specialized user to perform backups.
Creating a SQL Server login to perform backups.
You can use a sysadmin user to perform backups, but you may want to use a specialized user to do that.
To create a specialized backup login, just create that login and grant “db_backupoperator” in every database that is going to be backed up.
Preparing a SQL script
Now we will create the Backup script called Backup.sql.
As you can tell, the script is pretty adaptable, we can specify the path, name of the backup files/devices, retention days…
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directory SET @path = '\\Backups\' -- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('tempdb') -- exclude these databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '_FULL' + '.BAK' BACKUP DATABASE @name TO DISK = @fileName WITH RETAINDAYS = 40 FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
If you have any questions about the script, please, let us know in the comment section below.
Crontab launcher setup
Nothing special here. Just open your crontab as normal “crontab -e” and add the backup launcher we just create.
10 7 * * * /paht/to/LaunchBackup.sh
In this example, the backup will be launched everyday at 07:10.