使用PHP备份SQL Server数据库 [英] Backup SQL Server database using PHP

问题描述

我正在尝试使用PHP备份调用存储过程的MS SQL数据库.

I am trying to backup a MS SQL database calling a Stored Procedure using PHP.

当我在SSMS上执行de SP时，一切正常.但是，当我从PHP脚本中调用它时，可以看到一个".bak".在备份文件夹中，并且在PHP完成处理后，BackUp.bak文件消失了.

When I execute de SP on SSMS, everything works fine. However, when I call it from the PHP script, I can see a ".bak" in the backup folder and right after the PHP finishes processing, the BackUp.bak file disappears.

这是存储过程:

DECLARE @date VARCHAR(10)
SET @date = (SELECT date FROM tbl_date)

Declare @fileName VARCHAR(100)
SET @fileName = ('C:\db_backup\BackUp_' + @date + '.bak')

BACKUP DATABASE DbName
TO DISK = @fileName
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of DbName';

下面是我称为SP的PHP代码:

Below is the PHP code I call the SP:

$serverName = "server";
$connectionInfo = array( "Database"=>"DbName", "UID"=>"UserName", "PWD"=>"P@ssword", "CharacterSet"=>"UTF-8", "ReturnDatesAsStrings" => "false");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

$BackupDB = "Exec DBBackup";
sqlsrv_query($conn, $BackupDB);

我正在运行Apache 2.4.46和SQL Server Express 2019.

I am running Apache 2.4.46 and SQL Server Express 2019.

我的目标是创建一个".bak"使用PHP的文件.

My goal is to create a ".bak" file using PHP.

我尝试使用SP是因为原始代码是用Classic ASP编写的，并且可以通过这种方式完美地工作.

I tried using a SP because the original code is written in Classic ASP and it works flawlessly this way.

我愿意尝试不同的方法.

I am open to try different approaches.

谢谢.

推荐答案

说明:

您正在使用SQL Server的PHP驱动程序，因此以下说明是解决问题的附加选项:

You are using PHP Driver for SQL Server, so the following explanations are an additional option for solving your problem:

• 似乎此问题可能是特定于驱动程序的问题.SQL Server用批处理创建的结果集填充连接的输出缓冲区-有关受影响的行数的信息(例如，在 INSERT \ DELETE \ UPDATE 语句的情况下)或进度状态(从 BACKUP \ RESTORE DATABADE 语句返回).这些结果集必须由PHP脚本处理.看来，此行为是设计使然，PHP脚本应刷新所有未决的结果集.提取结果集后，SQL Server完成批处理的执行.您需要使用的适当函数/方法是 sqlsrv_next_result()(对于驱动程序的SQLSRV版本)和 PDOStatement :: nextRowset()(对于驱动程序的PDO_SQLSRV版本)).
• 对于驱动程序的SQLSRV版本，您需要使用 sqlsrv_configure("WarningsReturnAsErrors"，0); 来更改错误和警告处理.

• It seems that this issue is probably a driver specific problem. SQL Server fills the output buffer of the connection with the result sets that are created by the batch - information about the count of the affected rows (in a case of INSERT\DELETE\UPDATE statements for example) or progress status (returned from BACKUP\RESTORE DATABADE statements). These result sets must be processed by the PHP script. It seems that this behavior is by design and the PHP script should flush all the pending result sets. After the result sets are fetched, SQL Server completes the execution of the batch. The appropriate functions\methods that you need to use are sqlsrv_next_result() (for SQLSRV version of the driver) and PDOStatement::nextRowset() (for PDO_SQLSRV version of the driver).
• For the SQLSRV version of the driver you need to change the error and warning handling using sqlsrv_configure("WarningsReturnAsErrors", 0);.

示例:

我可以从问题中复制问题，以下示例是可行的解决方案:

I'm able to reproduce the issue from the question and the following examples are working solutions:

使用驱动程序的SQLSRV版本:

Using SQLSRV version of the driver:

<?php
// Server information
$server   = "server\instance,port";
$database = "database";
$uid      = "username"; 
$pwd      = "password";

// Configuration
sqlsrv_configure("WarningsReturnAsErrors", 0);

// Connection
$cinfo = array(
    "UID" => $uid,
    "PWD" => $pwd,
    "Database" => $database
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
    echo "Unable to connect.";
    die( print_r( sqlsrv_errors(), true));
}

// Statement
$sql = "
    DECLARE @date VARCHAR(19)
    SET @date = CONVERT(VARCHAR(19), GETDATE(), 126)
    SET @date = REPLACE(@date, ':', '-')
    SET @date = REPLACE(@date, 'T', '-')
    
    DECLARE @fileName VARCHAR(100)
    SET @fileName = ('d:\backup\BackUp_' + @date + '.bak')
    
    BACKUP DATABASE dbname
    TO DISK = @fileName
    WITH 
        FORMAT,
        STATS = 1, 
        MEDIANAME = 'SQLServerBackups',
        NAME = 'Full Backup of dbname';
";
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
    echo "Unable to execute query.";
    die( print_r( sqlsrv_errors(), true));
}

// Clear buffer
while (sqlsrv_next_result($stmt) != null){};
echo "Success";

// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

使用驱动程序的PDO_SQLSRV版本:

Using PDO_SQLSRV version of the driver:

<?php
// Server information
$server   = "server\instance,port";
$database = "database";
$uid      = "username"; 
$pwd      = "password";

// Connection
try {
    $conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die( "Error connecting to SQL Server".$e->getMessage());
}

// Statement
$sql = "
    DECLARE @date VARCHAR(19)
    SET @date = CONVERT(VARCHAR(19), GETDATE(), 126)
    SET @date = REPLACE(@date, ':', '-')
    SET @date = REPLACE(@date, 'T', '-')
    
    DECLARE @fileName VARCHAR(100)
    SET @fileName = ('d:\backup\BackUp_' + @date + '.bak')
    
    BACKUP DATABASE dbname
    TO DISK = @fileName
    WITH 
        FORMAT,
        STATS = 1, 
        MEDIANAME = 'SQLServerBackups',
        NAME = 'Full Backup of dbname';
";
try {
    $stmt = $conn->prepare($sql);
    $stmt->execute();
} catch (PDOException $e) {
    die ("Error executing query. ".$e->getMessage());
}

// Clear buffer
try {
    while ($stmt->nextRowset() != null){};
    echo "Success";
} catch (PDOException $e) {
    die ("Error executing query. ".$e->getMessage());
}

// End
$stmt = null;
$conn = null;
?>

这篇关于使用PHP备份SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！