Backup

Browse posts by tag

Backup Restore Database by sqlcmd

April 3, 2025

1. Taking Full Backups with sqlcmd

# Run the commands when you reach an important point in the database configuration
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "BACKUP DATABASE [v7.3.1_HUB_511_lab] TO DISK = '/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak' WITH FORMAT, INIT, NAME = 'Stage3';"
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "BACKUP DATABASE [HUB-5.1.1-lab] TO DISK = '/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_3.bak' WITH FORMAT, INIT, NAME = 'Stage3';"

# Check the result
sudo docker exec -it sql1 ls -l /var/opt/mssql/backups/

2. Restoring a Specific Backup

# Restore databases
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "RESTORE DATABASE [v7.3.1_HUB_511_lab] FROM DISK = '/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak' WITH REPLACE, RECOVERY;"
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "RESTORE DATABASE [HUB-5.1.1-lab] FROM DISK = '/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_3.bak' WITH REPLACE, RECOVERY;"

3. Restoring a Specific Backup via SSM

# Restore database via SSM
aws ssm send-command \
    --instance-ids "i-0e0df3af14a11b3d1" \
    --document-name "AWS-RunShellScript" \
    --parameters 'commands=[
        "sudo docker exec sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '\''Password123'\'' -Q \"RESTORE DATABASE [v7.3.1_HUB_511_lab] FROM DISK = '\''/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak'\'' WITH REPLACE, RECOVERY;\""
    ]' \
    --region "ap-southeast-2"

# Check the Log in case of failure
aws ssm list-command-invocations --command-id abab87ca-7abb-4746-8666-fa6ebbe67b51 --details

Copy Files from a Docker to S3

April 2, 2025

Backup files from Docker Container

  1. Login to the machine running the Docker Container
  2. Copy back files in Docker container to the current directory
sudo docker cp sql1:/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_2.bak ./HUB-5.1.1-lab_Stage_2.bak
sudo docker cp sql1:/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_3.bak ./HUB-5.1.1-lab_Stage_3.bak
sudo docker cp sql1:/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_4.bak ./HUB-5.1.1-lab_Stage_4.bak
sudo docker cp sql1:/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak ./v7.3.1_HUB_511_lab_Stage_3.bak
sudo docker cp sql1:/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_4.bak ./v7.3.1_HUB_511_lab_Stage_4.bak
  1. Upload them to S3 bucket
# Change the ownership of the files:
sudo chown ssm-user:ssm-user *.bak

# Create a timestamp variable
TIMESTAMP=$(date +%Y%m%d-%H%M%S)

# Upload both files to the timestamped folder
aws s3 cp HUB-5.1.1-lab_Stage_2.bak s3://gcs-share/db-backup/$TIMESTAMP/
aws s3 cp HUB-5.1.1-lab_Stage_3.bak s3://gcs-share/db-backup/$TIMESTAMP/
aws s3 cp HUB-5.1.1-lab_Stage_4.bak s3://gcs-share/db-backup/$TIMESTAMP/
aws s3 cp v7.3.1_HUB_511_lab_Stage_3.bak s3://gcs-share/db-backup/$TIMESTAMP/
aws s3 cp v7.3.1_HUB_511_lab_Stage_4.bak s3://gcs-share/db-backup/$TIMESTAMP/