php调用sqlserver备份数据库脚本,创建备份文件然后删除 [英] Php calling sqlserver backup database script, backup file created and then deleted
问题描述
我有一个php调用sqlserver备份脚本. 如果脚本直接从SSMS执行,则脚本成功创建了备份.但是,当通过php调用时,我可以看到在目标文件夹中创建的文件,但是似乎在php完成后,该文件也被删除了. 我在这里哪里做错了?
I have a php calling sqlserver backup script. The script, if executed directly from SSMS, it created the backups successfully. But, when called via php, I can see the file created on the destination folder, but it seems that when php finishes, the file also got deleted. Where do I do wrong here?
PHP:
$strSQL = file_get_contents("archdata.sql");
if (!empty($strSQL)) {
$query=$conn->prepare($strSQL);
if ($query->execute()) {
sleep(5);
echo "1";
} else {
echo "Error: " . $strSQL;
}
}
archdata.sql:
archdata.sql:
SET @path = 'C:\Data\backups\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @fileName = @path + 'ProdDB _' + @fileDate + '.BAK'
BACKUP DATABASE ProdDB TO DISK=@fileName WITH STATS = 1
推荐答案
以下是对我有用的解决方案:
Here is a solution, that works for me:
- 执行
sqlsrv_configure("WarningsReturnAsErrors", 0);
更改错误处理设置. - 从
BACKUP DATABASE
语句中删除WITH STATS = 1
.
- execute
sqlsrv_configure("WarningsReturnAsErrors", 0);
to change the settings for error handling. - remove
WITH STATS = 1
fromBACKUP DATABASE
statement.
我能够使用SQL Server的Apache 2.4,PHP 7.1.12和Microsoft PHP驱动程序(php_sqlsrv_71_ts_x86.dll,版本4.3)通过测试案例重现此问题.唯一的区别是该示例使用了SQLSRV驱动程序(我在测试环境中不能使用PDO_SQLSRV驱动程序).
I'm able to reproduce this issue with a test case using Apache 2.4, PHP 7.1.12 and Microsoft PHP Driver for SQL Server (php_sqlsrv_71_ts_x86.dll, version 4.3). The only difference is that the example uses SQLSRV Driver (I can't use PDO_SQLSRV Driver in my testing environment).
PHP
<?php
sqlsrv_configure("WarningsReturnAsErrors", 0);
// Connection
$serverName = "127.0.0.1\instance,1433";
$connectionInfo = array(
"UID"=>"user",
"PWD"=>"password",
"Database"=>"ProdDB"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
echo "Unable to connect.</br>";
die(var_export(sqlsrv_errors(), true));
}
// Backup database
$strSQL = file_get_contents("archdata.sql");
if (!empty($strSQL)) {
$query = sqlsrv_query($conn, $strSQL);
if ($query === false) {
die(var_export(sqlsrv_errors(), true));
} else {
sleep(5);
echo "Success";
}
}
?>
T-SQL(archdata.sql)
T-SQL (archdata.sql)
declare
@path varchar(100),
@fileDate varchar(20),
@fileName varchar(140)
SET @path = 'd:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112)
SET @fileName = @path + 'ProdDB_' + @fileDate + '.BAK'
BACKUP DATABASE ProdDB TO DISK=@fileName
别忘了给'D:\ Backup'文件夹必要的权限.
Don't forget to give necessary rights to 'D:\Backup' folder.
这篇关于php调用sqlserver备份数据库脚本,创建备份文件然后删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!