MSSQL数据库备份+日志搜索

DECLARE @dbName sysname = DB_NAME();
DECLARE @filePath NVARCHAR(260);

SET @filePath = 
    'D:\DB_Backup\'
    + @dbName + '_full_' 
    + REPLACE(REPLACE(REPLACE(CONVERT(varchar(20), GETDATE(), 20), '-', ''), ' ', '_'), ':', '') 
    + '.bak';

PRINT @filePath;

declare @bkname nvarchar(100)
set @bkname = @dbName + ' 完整备份'
BACKUP DATABASE @dbName
TO DISK = @filePath
WITH 
    RETAINDAYS = 15,
    NOFORMAT, NOINIT,
    NAME = @bkname,
    SKIP, NOREWIND, NOUNLOAD, STATS=10;
GO

-- 保存当前数据库名
DECLARE @dbName SYSNAME = DB_NAME();  

-- 保存当前恢复模式
DECLARE @recoveryModel NVARCHAR(20);
SELECT @recoveryModel = recovery_model_desc 
FROM sys.databases 
WHERE name = @dbName;

-- 设置为 SIMPLE 模式以截断日志
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY SIMPLE';
EXEC(@sql);

-- 获取日志文件逻辑名
DECLARE @logFileName NVARCHAR(128);
SELECT @logFileName = name 
FROM sys.database_files 
WHERE type_desc = 'LOG';

-- 收缩日志文件到最小
SET @sql = N'DBCC SHRINKFILE ([' + @logFileName + N'], 1, TRUNCATEONLY)';
EXEC(@sql);

-- 恢复原来的恢复模式（FULL 或 BULK_LOGGED）
SET @sql = N'ALTER DATABASE [' + @dbName + N'] SET RECOVERY ' + @recoveryModel;
EXEC(@sql);
GO

-- 允许 xp_cmdshell（如果没启用过）
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 删除一个月前的备份文件（假设备份目录是 D:\DB_Backup）
DECLARE @BackupPath NVARCHAR(255) = N'D:\DB_Backup';
DECLARE @cmd NVARCHAR(1000);

-- forfiles 命令：删除 30 天前的 .bak 文件
SET @cmd = 'forfiles /p "' + @BackupPath + '" /s /m *.bak /d -30 /c "cmd /c del @path"';
EXEC xp_cmdshell @cmd;