我之前帮团队解决过类似的需求，给你整理了一套完全符合要求的自动化备份方案——不用碰UI操作，团队成员只需要修改一个配置文件里的几个变量就能执行，还能自动排除待删除表、定期运行：

1. 可自定义的配置文件（团队仅需修改这个） #

创建一个名为BackupConfig.ps1的文件，所有需要调整的参数都在这里，成员不用碰备份脚本核心：

# 生产数据库连接字符串（修改为你的生产库地址、账号密码）
$connectionString = "Server=PROD-SQL-01;Database=ProductionDB;User Id=BackupUser;Password=YourStrongPassword;"

# 本地备份存储路径（确保有足够磁盘空间）
$backupRootPath = "D:\MSSQL_Backups"

# 待删除表的匹配规则（比如表名包含"_ToDelete"，可根据实际标记规则修改）
$excludeTablePattern = "*_ToDelete"

# 备份间隔（小时，比如24就是每天备份一次）
$backupIntervalHours = 24

# 备份文件保留天数（自动清理超过这个天数的旧备份）
$retentionDays = 7

# 数据库容量阈值提醒（可选，比如超过100GB时记录警告日志）
$capacityWarningThresholdGB = 100

2. 自动化备份核心脚本 #

创建BackupScript.ps1，这个脚本负责加载配置、查询排除表、执行备份、清理旧文件，团队不用修改：

# 加载配置文件
. .\BackupConfig.ps1

# 创建备份目录（按日期命名）
$backupDate = Get-Date -Format "yyyyMMdd_HHmmss"
$backupPath = Join-Path $backupRootPath $backupDate
New-Item -ItemType Directory -Path $backupPath -Force | Out-Null

# 导入SqlServer模块（需要提前安装：Install-Module -Name SqlServer）
Import-Module SqlServer -ErrorAction Stop

try {
    # 1. 查询生产库中需要排除的表
    $excludeTables = Invoke-SqlCmd -ConnectionString $connectionString -Query @"
        SELECT name 
        FROM sys.tables 
        WHERE name LIKE '$excludeTablePattern'
"@ | Select-Object -ExpandProperty name

    Write-Host "已识别待排除的表：$($excludeTables -join ', ')"

    # 2. 获取所有需要备份的表
    $allTables = Invoke-SqlCmd -ConnectionString $connectionString -Query @"
        SELECT name 
        FROM sys.tables 
        WHERE name NOT LIKE '$excludeTablePattern'
"@ | Select-Object -ExpandProperty name

    # 3. 逐个备份表（逻辑备份，生成带数据的脚本）
    foreach ($table in $allTables) {
        $backupFile = Join-Path $backupPath "$table.sql"
        Write-Host "正在备份表：$table -> $backupFile"
        Export-SqlScript -ServerInstance (Get-SqlConnection -ConnectionString $connectionString).DataSource `
            -Database (Get-SqlConnection -ConnectionString $connectionString).Database `
            -TableName $table `
            -OutputFile $backupFile `
            -ScriptData `
            -ErrorAction Stop
    }

    # 4. 检查数据库容量（超过阈值时记录警告）
    $dbSizeGB = Invoke-SqlCmd -ConnectionString $connectionString -Query @"
        SELECT SUM(size) * 8 / 1024 / 1024 AS SizeGB 
        FROM sys.database_files
"@ | Select-Object -ExpandProperty SizeGB

    if ($dbSizeGB -gt $capacityWarningThresholdGB) {
        $warningMsg = "警告：生产库容量已达$dbSizeGB GB，超过阈值$capacityWarningThresholdGB GB"
        Write-Warning $warningMsg
        Add-Content -Path (Join-Path $backupPath "BackupLog.txt") -Value "$(Get-Date): $warningMsg"
    }

    # 5. 清理过期备份文件
    $oldBackups = Get-ChildItem -Path $backupRootPath -Directory | Where-Object {
        $_.CreationTime -lt (Get-Date).AddDays(-$retentionDays)
    }
    foreach ($oldBackup in $oldBackups) {
        Write-Host "清理过期备份：$($oldBackup.FullName)"
        Remove-Item -Path $oldBackup.FullName -Recurse -Force
    }

    # 记录备份成功日志
    $successMsg = "备份完成，共备份$($allTables.Count)张表，存储路径：$backupPath"
    Write-Host $successMsg
    Add-Content -Path (Join-Path $backupPath "BackupLog.txt") -Value "$(Get-Date): $successMsg"
}
catch {
    $errorMsg = "备份失败：$($_.Exception.Message)"
    Write-Error $errorMsg
    Add-Content -Path (Join-Path $backupRootPath "ErrorLog.txt") -Value "$(Get-Date): $errorMsg"
    exit 1
}

3. 设置定期自动备份 #

Windows系统（任务计划程序） #

• 打开「任务计划程序」，创建基本任务
• 触发条件：选择「每天」（或按$backupIntervalHours调整频率），设置执行时间
• 操作：选择「启动程序」，程序/脚本填powershell.exe，添加参数填-ExecutionPolicy Bypass -File "C:\Path\To\BackupScript.ps1"
• 配置：勾选「不管用户是否登录都要运行」，确保有足够权限执行

Linux/macOS系统（Crontab） #

• 打开终端，执行crontab -e
• 添加定时规则（比如每天凌晨1点执行）：

  0 1 * * * pwsh /path/to/BackupScript.ps1
  

• 保存退出即可

4. 团队使用步骤 #

1. 把BackupConfig.ps1和BackupScript.ps1共享给团队成员
2. 成员仅需修改BackupConfig.ps1中的连接字符串、备份路径、排除规则这几个核心变量
3. 测试手动运行一次BackupScript.ps1确认能正常备份
4. 按照上面的步骤配置定期任务

注意事项 #

• 确保生产库的备份账号有db_backupoperator权限，以及查询系统表的权限
• 本地开发机需要提前安装SqlServer模块：Install-Module -Name SqlServer（需要管理员权限）
• 对于超过100GB的数据库，建议分表备份时启用压缩，或者在脚本中添加磁盘空间检查逻辑
• 定期检查备份日志和错误日志，确保备份任务正常运行

内容的提问来源于stack exchange，提问作者cdsln