PHP还原SQL Server数据库卡在‘正在还原’状态问题排查

问题描述 #

使用SQL Server Management Studio（SSMS）还原数据库时一切正常，但通过PHP调用sqlsrv_query系列函数执行还原操作时，数据库会卡在“正在还原...”状态，尝试访问该数据库时提示权限错误。

环境信息 #

• PHP版本：7.4.26
• SQL Server驱动：ODBC Driver 17 for SQL Server

代码片段 #

1. 存储过程创建SQL #

sqlsrv_configure("WarningsReturnAsErrors", 0);

$createSPwh = "CREATE OR ALTER PROCEDURE [dbo].[CreateEmptyDB_wh] 
@PackageName nvarchar(100)
AS
BEGIN
DECLARE @FullPackageName nvarchar(100)
SET @FullPackageName = @PackageName
DECLARE @PCKGNAMEDATE VARCHAR(100) = (SELECT LEFT(@FullPackageName, CHARINDEX('_', @FullPackageName) - 1) + 
        REPLACE(SUBSTRING(@FullPackageName, CHARINDEX('_', @FullPackageName), LEN(@FullPackageName)), '.bak', '') AS [FirstName])
        
DECLARE @PCKGNAME VARCHAR(100) = (SELECT REPLACE(@PCKGNAMEDATE,RIGHT(@PCKGNAMEDATE, CHARINDEX('_', REVERSE(@PCKGNAMEDATE)) - 1),''))
SET @PCKGNAME = (SELECT LEFT(@PCKGNAME, LEN(@PCKGNAME) - 1) )
DECLARE @DiskDrive VARCHAR(100) = 'E:\' + @FullPackageName
DECLARE @PackageNameWH VARCHAR(100) = @PCKGNAME + '_wh'
DECLARE @dbdatwh VARCHAR(100) = 'h:\mssql\data\' + @PCKGNAME+ '_wh.mdf'
DECLARE @dblogwh VARCHAR(100) = 'S:\MSSQL\Logs\' + @PCKGNAME+ '_wh.ldf'

BEGIN TRY  

RESTORE DATABASE @PackageNameWH
FROM DISK = @DiskDrive
WITH RECOVERY
,file=4,NOUNLOAD,
MOVE 'DB_WH_DAT' TO @dbdatwh,
MOVE 'DB_WH_log' TO @dblogwh

END TRY 
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END";

2. 创建存储过程的PHP代码 #

$stmtSPwh = sqlsrv_query($sqlconn, $createSPwh);

if($stmtSPwh === false) {
    echo "createSPwh";
    die(print_r(sqlsrv_errors(),true));
}

3. 执行还原操作的PHP代码 #

$tsql_callSP_wh = "EXEC dbo.CreateEmptyDB_wh @PackageName = ?";

$PackageName = "WarehouseDB.bak";
$params = array(array(&$PackageName, SQLSRV_PARAM_IN));

$stmt3 = sqlsrv_prepare($sqlconn, $tsql_callSP_wh, $params);
if (!sqlsrv_execute($stmt3)) {
    die(print_r(sqlsrv_errors(),true));
}

错误信息 #

执行PHP代码后无报错，但尝试访问目标数据库时收到以下错误：

Array
( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 5052 [code] => 5052 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Access is not permitted while a database is in the Restoring state. [message]

排查与解决方案 #

1. 强制开启自动提交 #

SQLSRV连接默认可能未开启自动提交，还原操作作为DDL操作需要事务正确提交。在执行还原前为连接开启自动提交：

// 全局配置自动提交
sqlsrv_configure('AutoCommit', true);
// 或针对当前连接单独设置
sqlsrv_set_option($sqlconn, SQLSRV_ATTR_AUTOCOMMIT, SQLSRV_AUTOCOMMIT_ON);

若连接处于未提交的事务中，还原操作会被挂起，导致数据库卡在还原状态。

2. 验证存储过程逻辑正确性 #

在SSMS中手动执行存储过程，传入相同参数确认还原是否正常：

EXEC dbo.CreateEmptyDB_wh @PackageName = 'WarehouseDB.bak'

若SSMS中执行也出现问题，说明存储过程存在逻辑错误：

• 检查file=4是否对应备份文件中存在的数据库文件
• 确认文件名解析逻辑是否正确生成了合法的数据库名、数据/日志文件路径
• 验证MOVE子句中的逻辑文件名（DB_WH_DAT、DB_WH_log）是否与备份文件中的实际逻辑名一致

3. 捕获存储过程的错误输出 #

当前PHP代码未获取存储过程中CATCH块返回的错误信息，添加代码捕获执行结果：

if (!sqlsrv_execute($stmt3)) {
    die(print_r(sqlsrv_errors(), true));
}
// 获取存储过程返回的错误详情
while ($row = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) {
    print_r($row);
}

通过此方式可获取还原过程中的具体错误，定位问题根源。

4. 检查SQL Server服务权限 #

确保SQL Server服务账号对以下路径拥有读写权限：

• 备份文件路径：E:\
• 数据文件路径：h:\mssql\data\
• 日志文件路径：S:\MSSQL\Logs\
  SSMS执行还原使用的是当前登录用户权限，而PHP调用时使用的是SQL Server服务账号权限，两者权限不一致可能导致文件写入失败，进而卡住还原过程。

5. 确保操作完成后再关闭连接 #

还原操作属于耗时操作，若PHP代码执行完成后提前关闭连接，会导致还原过程中断。可添加逻辑等待数据库状态变为在线：

// 生成目标数据库名
$dbName = str_replace('.bak', '_wh', $PackageName);
do {
    $checkStmt = sqlsrv_prepare($sqlconn, "SELECT state_desc FROM sys.databases WHERE name = ?", array($dbName));
    sqlsrv_execute($checkStmt);
    $state = sqlsrv_fetch_array($checkStmt)[0];
    if ($state === 'ONLINE') break;
    usleep(500000); // 等待500毫秒
} while (true);

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