Automate Backup database SQL Server in docker container

1. Write T-SQL for backup your database.

For example I use TutorialDB database for demonstrate purpose. Your can create same database click Create a database

PRINT "Start Backup process...";
DECLARE @MyFileName varchar(200)
SELECT @MyFileName=N'/tmp/Backup_' + convert(nvarchar(20),GetDate(),112)+'_'+convert(nvarchar(20),GetDate(),108)+ '.bak'
BACKUP DATABASE [TutorialDB] TO DISK=@MyFileName
PRINT "Finished backup process...";

Then save above T-SQL to backup-database.sql

2. Copy backup-database.sql file to your container.

For example I just copy it to tmp directory. You can copy it to any your directory.

docker cp ./backup-database.sql example-database:/tmp

Then go inside container by cli as below.

docker exec -it example-database /bin/bash
$ ls -lh /tmp

Then see in tmp directory as below.

3. Update packages list to new version.

Go inside your container by docker exec cli as below.

docker exec -it --user root example-database /bin/bash

Note: You need to use --user root (option) for update package list. Next, update package list by apt-get update -y as below.

Then you will installing 2 package such as vim and cron as below.

4. Add sqlcmd cli to crontab by cron -e cli as below.

For example above configuration is running backup-database.sql every 1 minute. Keep in mind it's just example for show how it work. Next you want to restart crontab for start your schedule job like this

5. See log by tail -f /tmp/daily-backup.log & .bak files.

Until we meet again. Thank you