目标：在 每月 1 号、15 号 00:01，对 SQL Server 做可审计、可回溯的安全重启；重启后自动拉起 SQL Server Agent；全程落盘日志，90 天自动清理。

1 环境与约束

系统：Windows Server（本例以 2019 为例）

SQL Server：默认实例（服务名 MSSQLSERVER）

部署路径：E:\Scripts

非 FCI、未在用 AG（若是 FCI/AG，方案需更换为群集/AG 感知版）

2 基本信息梳理

Windows PowerShell
版权所有 (C) Microsoft Corporation。保留所有权利。

PS C:\Users\Administrator>  Get-Service *sql*

Status   Name               DisplayName
------   ----               -----------
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)
Running  SQLBrowser         SQL Server Browser
Running  SQLSERVERAGENT     SQL Server 代理 (MSSQLSERVER)
Running  SQLTELEMETRY       SQL Server CEIP service (MSSQLSERVER)
Running  SQLWriter          SQL Server VSS Writer

CMD>
Microsoft Windows [版本 10.0.17763.107]
(c) 2018 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>sc query ClusSvc

SERVICE_NAME: ClusSvc
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING
                                (STOPPABLE, NOT_PAUSABLE, ACCEPTS_PRESHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

C:\Users\Administrator>sc query ClusSvc | findstr /i RUNNING
        STATE              : 4  RUNNING


SSMS SQL>
SELECT 
  SERVERPROPERTY('IsClustered')   AS IsClustered,   -- 1=FCI（群集实例）
  SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled; -- 1=启用 AlwaysOn(AG)
--这个SQL返回结果IsClustered=0，IsHadrEnabled=1


SELECT
 (SELECT COUNT(*) FROM sys.availability_groups)                AS AGs,
 (SELECT COUNT(*) FROM sys.availability_replicas)              AS Replicas,
 (SELECT COUNT(*) FROM sys.availability_databases_cluster)     AS AG_Databases,
 (SELECT COUNT(*) FROM sys.dm_hadr_database_replica_states
   WHERE is_local=1)    
--这个SQL返回结果都是0

3 最终版脚本（带日志 + 60 秒冷却 + 启动后探活 + Agent 强启 + 90 天清理）

@echo off
setlocal

REM === 日志目录与文件 ===
set DIR=D:\Scripts
set LOGDIR=%DIR%\logs
if not exist "%LOGDIR%" mkdir "%LOGDIR%"

for /f "tokens=2 delims==" %%i in ('wmic os get localdatetime /value') do set LDT=%%i
set TS=%LDT:~0,4%-%LDT:~4,2%-%LDT:~6,2%_%LDT:~8,2%%LDT:~10,2%%LDT:~12,2%
set LOG=%LOGDIR%\restart_%TS%.log

echo [%date% %time%] ==== START ==== >> "%LOG%"

REM === 默认实例；命名实例改 MSSQL$实例名 / SQLSERVERAGENT$实例名 ===
set MSSVC=MSSQLSERVER
set AGTSVC=SQLSERVERAGENT

REM === 如果根本没有安装 Agent 服务，则记录后跳过与 Agent 相关的操作
sc query "%AGTSVC%" >nul 2>&1
if errorlevel 1060 (
  echo [%date% %time%] WARNING: %AGTSVC% not found, skip agent handling. >> "%LOG%"
  set HAS_AGENT=0
) else (
  set HAS_AGENT=1
)

REM === 停止（先 Agent 再 SQL；Agent 不存在则跳过）
if "%HAS_AGENT%"=="1" (
  net stop "%AGTSVC%" /y >> "%LOG%" 2>&1
  set rcA_stop=%errorlevel%
) else (
  set rcA_stop=0
)

net stop "%MSSVC%" /y >> "%LOG%" 2>&1
set rcS_stop=%errorlevel%

REM 等60秒让句柄释放，留出一个“冷却期”服务刚被 net stop 停掉时，系统还在做扫尾工作。
timeout /t 60 /nobreak >nul

REM === 启动 SQL
net start "%MSSVC%" >> "%LOG%" 2>&1
set rcS_start=%errorlevel%

REM === 强制启用并启动 Agent（不管之前状态；Agent 不存在则跳过）
set rcA_cfg=0
set rcA_start=0
if "%HAS_AGENT%"=="1" (
  sc config "%AGTSVC%" start= auto >> "%LOG%" 2>&1
  set rcA_cfg=%errorlevel%
  net start "%AGTSVC%" >> "%LOG%" 2>&1
  set rcA_start=%errorlevel%
)

echo [%date% %time%] Codes: MSSQL stop=%rcS_stop% start=%rcS_start% ; AGENT cfg=%rcA_cfg% stop=%rcA_stop% start=%rcA_start% >> "%LOG%"

REM === 事件日志 & 返回码（SQL 启动失败则报错退出；Agent 失败仅记警告）
if not "%rcS_start%"=="0" (
  echo [%date% %time%] ERROR: MSSQLSERVER failed to start. >> "%LOG%"
  eventcreate /ID 2011 /L APPLICATION /T ERROR /SO "DBA-RestartSQL" /D "MSSQLSERVER start failed; log=%LOG%" >nul 2>&1
  exit /b 2
)

if "%HAS_AGENT%"=="1" if not "%rcA_start%"=="0" (
  eventcreate /ID 2012 /L APPLICATION /T WARNING /SO "DBA-RestartSQL" /D "SQLSERVERAGENT failed to start; log=%LOG%" >nul 2>&1
)

eventcreate /ID 2010 /L APPLICATION /T INFORMATION /SO "DBA-RestartSQL" /D "SQL Server restart OK; log=%LOG%" >nul 2>&1

REM === 日志保留 90 天
forfiles /p "%LOGDIR%" /m "*.log" /d -90 /c "cmd /c del /q @path" >nul 2>&1

echo [%date% %time%] ==== DONE ==== >> "%LOG%"
endlocal
exit /b 0

4 创建目录+把脚本放到指定目录

5 配置计划任务程序

6 重启后验证

验证重启结果:
看 E:\Scripts\logs\restart_*.log 是否出现 ==== DONE ====
“上次运行结果”显示 0x0 即成功。

在SSMS 跑以下SQL：

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;--返回上次重启的时间

SELECT MAX(agent_start_date) AS AgentStartTime
FROM msdb.dbo.syssessions;--返回上次重启的时间

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE', N'SQLServerAgent';  --返回running

SELECT name, create_date 
FROM sys.databases 
WHERE name = 'tempdb';--返回上次重启的时间

SELECT name, state_desc 
FROM sys.databases 
WHERE state_desc <> 'ONLINE';--无返回，理想结果返回0 行。


---------------------------------------------------------------------------------------
SELECT @@SERVICENAME        AS ServiceName,        -- MSSQLSERVER 或 实例名
       SERVERPROPERTY('InstanceName') AS InstanceName; -- 默认实例会是 NULL

若自动重启失败则立马手工重启（cmd里执行）：
net start MSSQLSERVER
net start SQLSERVERAGENT

sc query MSSQLSERVER | findstr /i STATE
sc query SQLSERVERAGENT | findstr /i STATE