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
sqlcmd -S SQLServer服务器地址 -U 账号 -P 密码 -i 运行sql文件(./db_backup.sql) -o 记录日志(./db_backup.log)
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