0

I created a script in PHP / PDO that executes a line of SQL to make a file backup of various databases. The list of database names and the datestamp with which to select the folder into which to place the file are passed as HTTP REQUEST array elements. The script works, but for some reason SQL Server is deleting the file as soon as the script finishes running. With Explorer open, I can watch the folder and file get created correctly, but the file disappears upon completion. Here's the 

$dbh = getPDOConnection();
$sql = "BACKUP DATABASE :database TO DISK = :path";
$sth = $dbh->prepare($sql);
$dt = $_REQUEST["dtStamp"];

$uncPath = "\\\\[unc server]\\c$\MSSQL\\Backup\\backups_$dt";

if (!file_exists($uncPath)) {
    mkdir($uncPath, null, true);
}
$responses = [];
foreach(explode(",", $_REQUEST["databases"]) AS $d=>$database) {
    $sqlPath = "c:\\MSSQL\\Backup\\backups_$dt\\$database.bak";

    $sth->bindValue(":database", $database);
    $sth->bindValue(":path", $sqlPath);
    $sth->execute();

    $responses[] = $sth->fetch(PDO::FETCH_ASSOC);
}

Can anyone point me to what I'm missing? I can't seem to find any documentation from Microsoft that indicates why this behavior would occur.

Michelle
  • 57
  • 1
  • 7
  • 1
    1) will each backup overwrite each other - if backing up to same file? 2) Also you you should always check the return value of `$sth->execute()`. – bcperth Oct 17 '18 at 22:53
  • 1) Each time the page that executes this script is loaded, it creates a new datetime stamp. It's still possible to overwrite the file by running the script on the same database selection prior to reloading the page, but as this is purely an administrative page, I'm not concerned about that yet. 2) Good call, I'll add the return value of $sth->execute() to the status echo (not shown) at the end of the script. – Michelle Oct 17 '18 at 22:57
  • 1
    Also echo the value of `$database` on each loop to make sure that is as you expect. – bcperth Oct 17 '18 at 23:00
  • The fact that it's not echoed in this code is just an artifact of the code originally having been written to pass the database and path names to a stored procedure, which returned both the $database value and a couple of other values. It suffered from the same problem with deleting the file at the end of the procedure, so I created this version. – Michelle Oct 17 '18 at 23:03

0 Answers0