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.