当前位置：首页 > news >正文

SQLserver数据库巡检脚本

news 2025/6/26 22:41:01

SQL Server数据库巡检脚本的示例：

#!/bin/bash# 设置SQL Server登录凭证
SQL_USER="your_username"
SQL_PASSWORD="your_password"# 设置巡检结果输出文件路径
OUTPUT_FILE="/path/to/output.log"# 获取SQL Server版本信息
sql_version=$(sqlcmd -S localhost -U $SQL_USER -P $SQL_PASSWORD -Q "SELECT @@VERSION;" | grep -o 'Microsoft SQL Server.*')# 获取数据库列表
databases=$(sqlcmd -S localhost -U $SQL_USER -P $SQL_PASSWORD -Q "SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');")# 巡检开始时间
start_time=$(date +"%Y-%m-%d %H:%M:%S")# 输出巡检结果到文件
echo "SQL Server Database Inspection Report" > $OUTPUT_FILE
echo "Start Time: $start_time" >> $OUTPUT_FILE
echo "SQL Server Version: $sql_version" >> $OUTPUT_FILE
echo "" >> $OUTPUT_FILE# 遍历数据库列表进行巡检
for db in $databases
doecho "Checking database: $db"echo "===================================" >> $OUTPUT_FILEecho "Database: $db" >> $OUTPUT_FILE# 获取数据库大小size_info=$(sqlcmd -S localhost -U $SQL_USER -P $SQL_PASSWORD -Q "SELECT DB_NAME() AS DatabaseName, CONVERT(VARCHAR,SUM(size*8)/1024/1024)+' MB' AS Size FROM sys.master_files WHERE type = 0 AND DB_NAME() = '$db' GROUP BY DB_NAME();")echo "Database Size:" >> $OUTPUT_FILEecho "$size_info" >> $OUTPUT_FILE# 获取数据库日志文件大小log_size_info=$(sqlcmd -S localhost -U $SQL_USER -P $SQL_PASSWORD -Q "SELECT DB_NAME() AS DatabaseName, CONVERT(VARCHAR,SUM(size*8)/1024/1024)+' MB' AS Size FROM sys.master_files WHERE type = 1 AND DB_NAME() = '$db' GROUP BY DB_NAME();")echo "Log File Size:" >> $OUTPUT_FILEecho "$log_size_info" >> $OUTPUT_FILE# 获取数据库备份信息backup_info=$(sqlcmd -S localhost -U $SQL_USER -P $SQL_PASSWORD -Q "EXEC sp_msforeachdb 'USE [?]; SELECT DB_NAME() AS DatabaseName, MAX(backup_start_date) AS LastBackup FROM msdb.dbo.backupset WHERE type = ''D'' AND database_name = ''?'' GROUP BY DB_NAME();'")echo "Last Backup:" >> $OUTPUT_FILEecho "$backup_info" >> $OUTPUT_FILEecho "===================================" >> $OUTPUT_FILEecho "" >> $OUTPUT_FILE
done# 巡检结束时间
end_time=$(date +"%Y-%m-%d %H:%M:%S")
echo "End Time: $end_time" >> $OUTPUT_FILEecho "Database inspection completed. Please check the output file: $OUTPUT_FILE"

请根据需要修改 SQL_USER、SQL_PASSWORD 和 OUTPUT_FILE 变量为你的SQL Server登录凭证和输出文件路径。运行脚本后，将会生成一个巡检报告文件，其中包含了SQL Server版本、每个数据库的大小、日志文件大小和最近的备份日期等信息。

这只是一个基本的示例脚本，你可以根据具体需求进行扩展和定制，添加更多的巡检项和检查规则。

查看全文

http://www.lryc.cn/news/108013.html