SAP HANA 自动备份

1.创建db 账号 dbbackup ，授予backup admin、catalog read 系统权限

CREATE USER dbbackup PASSWORD 'password'

grant backup admin to dbbackup

grant catalog read to dbbackup

alter user dbbackup DISABLE PASSWORD LIFETIME

2、使用hdbuserstore 创建key sys_backup,prd_backup,po_backup用于连接数据库

3、构建备份脚本full_backup.sh

--daily full backup with file

#!/bin/bash

--获取当前的系统时间

prefix=`date +%Y_%m_%d_%H_%M_%S`

#指定连接数据库的key

sys_key='sys_backup'

prd_key='prd_backup'

pop_key='pop_backup'

#备份系统库SYSTEMDB

hdbsql -U $sys_key  "backup data using file ('COMPLETE_DATA_BACKUP_$prefix')"

sys_backupid=`hdbsql -U backup "select top 1 TO_BIGINT(REPLACE(BACKUP_ID,',','')) as backup_id from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc" | sed -n '2p'`

#清理超过7天的备份文件以及catalog

hdbsql -U $sys_key "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $sys_backupid COMPLETE"

#备份租户库PRD

hdbsql -U $prd_key  "backup data  using file ('COMPLETE_DATA_BACKUP_$prefix')"

prd_backupid=`hdbsql -U $prd_key "select top 1 TO_BIGINT(REPLACE(BACKUP_ID,',','')) as backup_id from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc" | sed -n '2p'`

#清理超过7天的备份文件以及catalog

hdbsql -U $prd_key "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $prd_backupid COMPLETE"

#备份租户库POP

hdbsql -U $pop_key  "backup data using file ('COMPLETE_DATA_BACKUP_$prefix')"

pop_backupid=`hdbsql -U test2backup "select top 1 TO_BIGINT(REPLACE(BACKUP_ID,',','')) as backup_id from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc" | sed -n '2p'`

#清理超过7天的备份文件以及catalog

hdbsql -U $pop_key "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $pop_backupid COMPLETE"

--daily full backup with backint

#!/bin/bash

#获取当前的系统时间

prefix=`date +%Y_%m_%d_%H_%M_%S`

#指定连接数据库的key

sys_key='sys_backup'

prd_key='prd_backup'

pop_key='pop_backup'

#备份系统库SYSTEMDB

hdbsql -U $sys_key  "backup data using backint ('COMPLETE_DATA_BACKUP_$prefix')"

sys_backupid=`hdbsql -U backup "select top 1 TO_BIGINT(REPLACE(BACKUP_ID,',','')) as backup_id from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc" | sed -n '2p'`

#清理超过7天的备份文件以及catalog

hdbsql -U $sys_key "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $sys_backupid COMPLETE"

#备份租户库PRD

hdbsql -U $prd_key  "backup data  using backint ('COMPLETE_DATA_BACKUP_$prefix')"

prd_backupid=`hdbsql -U $prd_key "select top 1 TO_BIGINT(REPLACE(BACKUP_ID,',','')) as backup_id from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc" | sed -n '2p'`

#清理超过7天的备份文件以及catalog

hdbsql -U $prd_key "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $prd_backupid COMPLETE"

#备份租户库POP

hdbsql -U $pop_key  "backup data using backint ('COMPLETE_DATA_BACKUP_$prefix')"

pop_backupid=`hdbsql -U test2backup "select top 1 TO_BIGINT(REPLACE(BACKUP_ID,',','')) as backup_id from M_BACKUP_CATALOG where ENTRY_TYPE_NAME='complete data backup' and UTC_START_TIME <= ADD_DAYS(CURRENT_UTCDATE,-7) order by UTC_END_TIME desc" | sed -n '2p'`

#清理超过7天的备份文件以及catalog

hdbsql -U $pop_key "BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $pop_backupid COMPLETE"

4、创建定时cron任务

crontab -e

###########daily full backup database at 23:30 ############

30 23 * * * /u03/dba_scripts/hana/full_backup.sh