SQL Server存储过程备份通过Laravel API调用后.bak文件消失问题

问题描述 #

在Microsoft SQL Server中编写的数据库备份存储过程，通过批处理直接执行时可正常生成.bak备份文件；但通过PHP Laravel API调用该存储过程时，.bak文件仅短暂出现后即消失，最终无留存。

可能原因及对应解决方法 #

1. Laravel语句执行机制干扰 #

DB::statement()会对SQL语句做预处理或封装，可能干扰备份操作的执行上下文。

解决方法：
使用DB::unprepared()替代DB::statement()，直接执行原始SQL语句：

public function backupDevHubdb(){
    try{
        $responseDB = DB::unprepared("EXEC [SP_CM_BATCH_DAILY] @iFlag = 'BACKUP_FULL'");
        return response("BACKUP COMPLETED ".$responseDB);
    }
    catch(Exception $e){
        return response($e->getMessage());
    }
}

2. SQL Server服务账户权限不足 #

备份文件的写入依赖SQL Server服务账户的权限：直接执行存储过程时（如SSMS、sqlcmd）通常使用Windows身份验证，服务账户权限充足；但Laravel调用若使用SQL身份验证，可能因服务账户对D:\FullBackup\文件夹权限不足，导致文件写入后被系统回收。

解决方法：

1. 打开D:\FullBackup\文件夹属性 → 安全选项卡
2. 添加SQL Server服务账户（默认通常为NT Service\MSSQLSERVER），授予完全控制权限
3. 应用权限后重新测试

3. 备份未完成即被连接中断 #

Laravel请求结束后会快速关闭数据库连接，可能导致SQL Server的备份操作未完全写入磁盘就被终止，临时文件被清理。

解决方法：

• 修改存储过程，添加CHECKPOINT强制写入磁盘：

BACKUP_FULL:
BEGIN
    DECLARE @name NVARCHAR(256) -- database name  
    DECLARE @path NVARCHAR(512) -- path for backup files  
    DECLARE @fileName NVARCHAR(512) -- filename for backup  
    DECLARE @fileDate NVARCHAR(40) -- used for file name
 
    SET @path = 'D:\FullBackup\'
    SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
    DECLARE db_cursor CURSOR READ_ONLY FOR  
    SELECT name 
    FROM master.sys.databases 
    WHERE name NOT IN ('master','model','msdb','tempdb')
    AND state = 0 
    AND is_in_standby = 0 
 
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name   
 
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       -- 强制写入磁盘，确保备份完成
       CHECKPOINT;
       FETCH NEXT FROM db_cursor INTO @name   
    END   
 
    CLOSE db_cursor   
    DEALLOCATE db_cursor
END

• 在Laravel控制器中延长请求超时时间：

public function backupDevHubdb(){
    // 设置超时时间为5分钟（300秒）
    set_time_limit(300);
    try{
        $responseDB = DB::unprepared("EXEC [SP_CM_BATCH_DAILY] @iFlag = 'BACKUP_FULL'");
        return response("BACKUP COMPLETED ".$responseDB);
    }
    catch(Exception $e){
        return response($e->getMessage());
    }
}

4. 安全软件误删备份文件 #

杀毒软件、防火墙或系统安全工具可能将新生成的.bak文件误判为恶意文件并自动删除。

解决方法：

• 将D:\FullBackup\文件夹添加到安全软件的白名单
• 临时关闭安全软件测试，确认是否为误删导致

5. 隐式事务干扰 #

虽然SQL Server的BACKUP操作不受事务回滚影响，但隐式事务可能导致执行上下文异常。

解决方法：
在存储过程的BACKUP_FULL块开头禁用隐式事务：

BACKUP_FULL:
BEGIN
    SET IMPLICIT_TRANSACTIONS OFF;
    DECLARE @name NVARCHAR(256) -- database name  
    -- 后续代码保持不变

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