数据库备份

Windows

Mysql

@echo off
setlocal
 
:: 设置MySQL的安装路径
set MYSQL_HOME=D:\SoftWare\mysql111\bin
 
:: 设置mysqldump的路径
set MYSQLDUMP_PATH=%MYSQL_HOME%\mysqldump.exe
 
:: 设置备份文件夹路径
set BACKUP_DIR=D:\a\xxx
 
:: 设置数据库的用户名和密码
set DB_USER=root
set DB_PASS=1234567
 
:: 设置要备份的数据库名
set DB_NAME=test
 
:: 创建备份文件夹
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"
 
:: 设置备份文件名
set BACKUP_FILE=%DB_NAME%_%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%.sql
set BACKUP_PATH=%BACKUP_DIR%\%BACKUP_FILE%
 
:: 执行mysqldump创建备份
"%MYSQLDUMP_PATH%" -u%DB_USER% -p%DB_PASS% --opt %DB_NAME% > "%BACKUP_PATH%"
 
:: 删除超过30天的备份文件夹
forfiles /p "%BACKUP_DIR%" /s /m *.* /d -30 /c "cmd /c IF @isdir == TRUE rd /s /q @path"
 
endlocal

SQL Server

# sqlserver.bat
sqlcmd -S SQLServer服务器地址 -U 账号 -P 密码 -i 运行sql文件(./db_backup.sql) -o 记录日志(./db_backup.log)

# db_backup.sql

# 数据库名称
DECLARE @backupDatabase VARCHAR(20) = 'test'
# 保存文件路径
DECLARE @backupBasePath VARCHAR(MAX) = 'D:\a\'
# 设置保存文件名称
DECLARE @backupFileName VARCHAR(50) = NULL
DECLARE @backupTime DATETIME = CURRENT_TIMESTAMP
SET @backupFileName = @backupDatabase + '_backup_' + CONVERT(VARCHAR(25), @backupTime, 112) + '_' + REPLACE(CONVERT(VARCHAR(25), CURRENT_TIMESTAMP, 114), ':', '') + '.bak'
# 保存路径
SET @backupBasePath = @backupBasePath + @backupFileName
# 执行
BACKUP DATABASE @backupDatabase TO DISK = @backupBasePath

@echo off  
chcp 65001  
setlocal  

sqlcmd -S DESKTOP-81MQ0TL -U sa -P 1234567 -i ./db_backup.sql -o ./db_backup.log

:: 设置备份文件所在的路径  
set "backupPath=D:\Debug\backup\SQL\"  
  
:: 计算要删除文件的日期界限（30天前）  
for /f "usebackq delims=" %%a in (`powershell -Command "(Get-Date).AddDays(-30).ToString('yyyy-MM-dd')"`) do set "deleteBefore=%%a"  
  
:: 使用PowerShell脚本来删除超过30天的.bak文件  
powershell -Command "Get-ChildItem -Path '%backupPath%' -Filter '*.bak' | Where-Object { !$_.PSIsContainer -and $_.LastWriteTime -lt [datetime]::ParseExact('%deleteBefore%', 'yyyy-MM-dd', $null) } | Remove-Item -Force"  
  
if ($LastExitCode -eq 0) (  
    echo 已删除超过30天的.bak文件。  
) else (  
    echo 删除文件时出现错误。  
)  
  
endlocal

pause