Backup and restore SQL server database using php

Question

I wanted to make a simple website, which can open, show, and run sql server queries, just like executing queries from sql server management studio. It already can execute some queries like select (then show it as a table), insert, update, delete, including when i use where/group/order statement. But, when I doing backup on my database with query :

BACKUP DATABASE 'mydb' TO DISK='pathname\filename.bak' WITH INIT/NOINIT

It can only backup the database once, and then when I execute the query with the same inputs, it got out with no error, but also no file backups produced from the execution. I don't know why, but when i'm executing with the same query from sql server management studio (with different filename so that the previous file didn't get overwritten), it execute without error and creating another file backup.

The code : php

$sql = "ALTER DATABASE $db_name SET RECOVERY FULL"; 
$stmt = sqlsrv_query($conn, $sql); 
if($stmt === false) 
{ 
die(print_r(sqlsrv_errors())); 
} 
else 
{ 
echo "Recovery model set to FULL"; 
}
if($_POST['type']=="Backup Full")
{
$query="BACKUP DATABASE $db_name TO DISK='$file\\$filename.bak' WITH
NOINIT;";
    echo "<br>".$query."<br>";
    $stat=sqlsrv_query($conn,$query);
    if($stat == false)
    {
        echo "Error to retrieve info, THIS IS THE ERROR : <br />";
        die(print_r(sqlsrv_errors()));
    }
    else
    {
        echo "Success!";
    }
    }

It create a new file .bak filled with my database backup
It returns error inside the 'if'
When i tried to run the code again with the different filename, it didn't create anymore .bak file.
When i restarted my PC, things back to normal (creating another .bak again)

And you code... ?

Felippe Duarte
– Felippe Duarte

2017-01-25 16:40:57 +00:00
Commented Jan 25, 2017 at 16:40 — Felippe Duarte
– Felippe Duarte, Commented Jan 25, 2017 at 16:40
Show your existing code...

Ishtiyaq Husain
– Ishtiyaq Husain

2017-01-25 17:09:54 +00:00
Commented Jan 25, 2017 at 17:09 — Ishtiyaq Husain
– Ishtiyaq Husain, Commented Jan 25, 2017 at 17:09

Jordi Espada · Accepted Answer · 2020-02-06 12:06:58Z

3

It seems there's a solution here. Your issue it happened to me also but in my case sometimes the backup was created and sometimes no and I guess that the backup process fails when php executes and it exits before the ending backup process, therefore, it kills the connection in the middle of backup process. I realized that to execute a sql sequence of no-query type, it has to be consumed with the instruction sqlsrv_next_result till there's no more results.

You should use this snippet in order to execute the backup query successfully.

$query="BACKUP DATABASE $db_name TO DISK='$file\\$filename.bak' WITH NOINIT;";

if ( ($stmt = sqlsrv_query($conn, $query)) )
{
    do 
    {
    } while ( sqlsrv_next_result($stmt) ) ;
    sqlsrv_free_stmt($stmt);
}else{
    die(print_r(sqlsrv_errors())); 
}

edited Feb 6, 2020 at 12:06

answered Feb 6, 2020 at 11:11

Jordi Espada

4155 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

matty Over a year ago

And if using pdo, you can do something like while ($stmt->nextRowset()) {}.

Collectives™ on Stack Overflow

Backup and restore SQL server database using php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related