backup_db.sh 1.81 KB
Newer Older
pasden's avatar
pasden committed
1
2
#!/bin/sh

pasden's avatar
pasden committed
3
4
5
6
7
8
9
CONTAINER_NAME="itinvent"
PATH_DOCKER_CONTAINERS="/volume1/docker"
BACKUP_PATH_INSIDE_CONTAINER="/var/opt/mssql/backup"
SQL_USER="sa"
SQL_PASSWORD="yourStrong(!)Password"
SQL_DATABASE="ITINVENT"
SQL_PORT="1433"
pasden's avatar
pasden committed
10

pasden's avatar
pasden committed
11
CONTAINER_DATA_PATH="${PATH_DOCKER_CONTAINERS}/${CONTAINER_NAME}"
pasden's avatar
pasden committed
12

pasden's avatar
pasden committed
13
echo "--"
pasden's avatar
pasden committed
14
15
16
17
18
docker exec -t $CONTAINER_NAME /opt/mssql-tools/bin/sqlcmd \
-S "localhost,${SQL_PORT}" \
-U "${SQL_USER}" \
-P "${SQL_PASSWORD}" \
-d "${SQL_DATABASE}" \
pasden's avatar
pasden committed
19
-Q "
pasden's avatar
pasden committed
20
DECLARE @dateTime VARCHAR(19) = CONVERT(VARCHAR(19), SYSDATETIMEOFFSET() AT TIME ZONE 'Russian Standard Time', 20)
pasden's avatar
pasden committed
21
DECLARE @dateBackup VARCHAR(MAX) = REPLACE(REPLACE(@dateTime,':','-'),' ','_')
pasden's avatar
pasden committed
22
23
DECLARE @fullPathBackupFile VARCHAR(MAX) = N'${BACKUP_PATH_INSIDE_CONTAINER}/backup_${SQL_DATABASE}_' + @dateBackup + '.bak'
BACKUP DATABASE ${SQL_DATABASE} TO DISK = @fullPathBackupFile WITH NOFORMAT, NOINIT, NAME = 'Полная База данных. Резервное копирование.', SKIP, NOREWIND, NOUNLOAD, STATS = 10
pasden's avatar
pasden committed
24
DECLARE @backupSetId AS INT
pasden's avatar
pasden committed
25
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name='${SQL_DATABASE}' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name='${SQL_DATABASE}')
pasden's avatar
pasden committed
26
IF @backupSetId IS NULL BEGIN RAISERROR(N'Ошибка верификации. Сведения о резервном копировании для базы данных не найдены.', 16, 1) END
pasden's avatar
pasden committed
27
RESTORE VERIFYONLY FROM DISK = @fullPathBackupFile WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
pasden's avatar
pasden committed
28
" \
pasden's avatar
pasden committed
29
-o "${BACKUP_PATH_INSIDE_CONTAINER}/backup_${SQL_DATABASE}_$(date +'%Y-%m-%d_%H-%M-%S').log"
pasden's avatar
pasden committed
30

pasden's avatar
pasden committed
31
32
find ${CONTAINER_DATA_PATH}/backup -type f -name "*.log" -mmin +480 -exec rm -f {} \; && echo "remove old .log files!"
find ${CONTAINER_DATA_PATH}/backup -type f -name "*.bak" -mmin +480 -exec rm -f {} \; && echo "remove old .bak files!"