sqlserver备份还原（解压上传到阿里云）

1.完全备份

mssql_backup.sh

#!/bin/bash
current_time=$(date "+%Y%m%d-%H%M%S")
backup_dir='/cygdrive/d/backup/'
backup_zip_dir='/cygdrive/d/backup/Archive/'
backup_archive_dir='/cygdrive/d/backup/Archive/'
backup_cygwin_dir=${backup_archive_dir}${current_time}
backup_win_dir='d:\backup\Archive\'${current_time}
backup_db=(WHQJAccountsDB WHQJAgentDB WHQJGameMatchDB WHQJGameScoreDB WHQJGroupDB WHQJNativeWebDB WHQJPlatformDB WHQJPlatformManagerDB WHQJRecordDB WHQJTreasureDB)
if [ ! -d ${backup_archive_dir} ]; then
mkdir -p ${backup_archive_dir} && chmod 777 ${backup_archive_dir}
fi
mkdir -p ${backup_cygwin_dir}
chmod 777 ${backup_cygwin_dir}
for element in ${backup_db[@]}
do
echo "backup "${element}
sqlcmd -S . -E -Q "BACKUP DATABASE ${element} TO DISK='${backup_win_dir}\\${element}.bak'"
done
cd $backup_zip_dir
7z a ${current_time}.7z ${current_time} -sdel
./ossutil64 cp ${current_time}.7z oss://product-db-backup/test-mssql/

 

 

2.完全备份还原

mssql_restore.sh

#!/bin/bash
backup_dir="/cygdrive/d/backup11/backup11"
backup_win_dir="d:\\backup11\\backup11\\"
for sub_path in `ls ${backup_dir}`
do
database_name=`echo ${sub_path}|awk -F . '{print $1}'`
echo "database_name is ${database_name}"
echo "bak name is ${sub_path}"
sqlcmd -S . -E -Q "RESTORE DATABASE ${database_name} FROM DISK='${backup_win_dir}${sub_path}'"
if [ $? -eq 0 ];then
echo "succeful restore ${database_name}!!"
fi
sleep 5
done

 

3.差异备份

sql_server_diff_backup.sh

#!/bin/bash
backup_dir="/cygdrive/d/backup/diff"
backup_win_dir="d:\\backup\\diff"

sql_db=(WHQJAccountsDB WHQJAgentDB WHQJGameMatchDB WHQJGameScoreDB WHQJGroupDB WHQJNativeWebDB WHQJPlatformDB WHQJPlatformManagerDB WHQJRecordDB WHQJTreasureDB)
read -p "请输入你要恢复的库名（库名/all为所有库）:" restore_db
read -p "请输入你要恢复到的目录数（file=？）:" number
if [ ${restore_db} == "all" ];then
for i in ${sql_db[@]}
do
echo "##########开始恢复##########"
#备份结尾日志
sqlcmd -S . -E -Q "backup log ${i} to disk='d:\\backup\\diff\\${i}.bak' with norecovery"
for j in `seq ${number}`
do
if [ ${number} == ${j} ];then
sqlcmd -S . -E -Q "RESTORE database ${i} FROM DISK = 'd:\\backup\\diff\\${i}.bak' WITH FILE=${j}, RECOVERY;"
else
sqlcmd -S . -E -Q "restore database ${i} from disk = 'd:\\backup\\diff\\${i}.bak' with file=${j}, norecovery"
fi
done
done
else
if [ -f d:\\backup\\diff\\${restore_db}.bak ];then
sqlcmd -S . -E -Q "backup log ${restore_db} to disk='d:\\backup\\diff\\${restore_db}.bak' with norecovery"
for j in `seq ${number}`
do
if [ ${number} == ${j} ];then
sqlcmd -S . -E -Q "RESTORE database ${restore_db} FROM DISK = 'd:\\backup\\diff\\${restore_db}.bak' WITH FILE=${j}, RECOVERY;"
else
sqlcmd -S . -E -Q "restore database ${restore_db} from disk = 'd:\\backup\\diff\\${restore_db}.bak' with file=${j}, norecovery"
fi
done
else
echo "请输入正确的库名!"
fi
fi

4.差异备份还原

restore_diff_sqlserver.sh

#!/bin/bash
backup_dir="/cygdrive/d/backup/diff"
backup_win_dir="d:\\backup\\diff"
if [ ! -d ${backup_dir} ];then
mkdir ${backup_dir}
chmod 777 ${backup_dir}
fi

sql_db=(WHQJAccountsDB WHQJAgentDB WHQJGameMatchDB WHQJGameScoreDB WHQJGroupDB WHQJNativeWebDB WHQJPlatformDB WHQJPlatformManagerDB WHQJRecordDB WHQJTreasureDB)
for i in ${sql_db[@]}
do
if [ ! -f ${backup_win_dir}\\${i}.bak ];then
#第一次完全备份
sqlcmd -S . -E -Q "backup database ${i} to disk='d:\\backup\\diff\\${i}.bak' with init"
echo ""> d:\\backup\\diff\\file.txt
#在完:全备份基础上进行差异备份
else
sqlcmd -S . -E -Q "backup database ${i} to disk='d:\\backup\\diff\\${i}.bak' with differential"
fi
done