1

When I restore a database with the SQL Server Management Studio it works just fine.
But when I restore it from the php ( using the function sqlsrv_query ), the database gets stuck on "Restoring..." state.

The sql query I use is this one:

RESTORE DATABASE WEB FROM DISK = 'C:\Program Files (x86)\Zend\Apache24\htdocs\db\WEB.bak' WITH REPLACE,RECOVERY

I also tried to login with the SQL Server Authentication mode using the same user I use with the php ( on sqlsrv_connect ) and to execute the same query, it worked. Only PHP does the problem.

PHP Code:

<?php
$connection_info = array( "Database" => "master", "UID" => "sa", "PWD" => "workingpw" );
$sql_handle = sqlsrv_connect( "DESKTOP\SQLEXPRESS", $connection_info );
if( !$sql_handle ) die();
sqlsrv_configure( "WarningsReturnAsErrors", 0 );

sqlsrv_query($sql_handle, "RESTORE DATABASE WEB FROM DISK = '" . getcwd() . "\\db\\WEB.bak' WITH REPLACE,RECOVERY");
$stmt = sqlsrv_query($sql_handle, "USE WEB");

if($stmt === false)
{
    die(print_r(sqlsrv_errors())); 
}
else echo "Success!";
?>


After PHP execution the error
"[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Database 'WEB' cannot be opened. It is in the middle of a restore." occurs. Restoring state

Please help.

tytopoo
  • 39
  • 5
  • dont you have to run `USE` after restore? –  Nov 05 '17 at 21:43
  • @nogad no, also, as said the query runs perfectly on the SSMS. – tytopoo Nov 05 '17 at 21:54
  • Microsoft seems to think so: https://blogs.msdn.microsoft.com/brian_swan/2010/07/01/restoring-a-sql-server-database-from-php/ –  Nov 05 '17 at 22:09
  • @nogad it was just for checking if the database was restored successfully, it's not necessary. – tytopoo Nov 05 '17 at 22:13
  • "The statement executed without error, but it left the database in the “restoring” state, and therefore unusable. The fix for this problem is to simple execute a “USE ” statement after the RESTORE statement. " this to me describes your exact issue –  Nov 05 '17 at 22:16
  • @tytopoo, the purpose of the `USE` is not to check the database was restored properly but to give the PHP driver a kick in the pants so that it consumes the messages returned by SQL Server and complete the restore operation. It is a workaround for the PHP driver issue and not generally needed. – Dan Guzman Nov 05 '17 at 23:20
  • @DanGuzman also after I'm using the USE command the database is in "Restoring..." state. – tytopoo Nov 06 '17 at 13:08
  • @tytopoo, did you also add `sqlsrv_configure( "WarningsReturnAsErrors", 0 ); ` as described in the blog article? – Dan Guzman Nov 06 '17 at 13:15
  • @DanGuzman Yes, I did and yet the restoring state occurs. – tytopoo Nov 06 '17 at 13:16
  • @tytopoo, did you refresh SSMS too? – Dan Guzman Nov 06 '17 at 13:20
  • @DanGuzman Of course, otherwise I couldn't see the "Restoring..." state, I refresh it once in a few seconds. – tytopoo Nov 06 '17 at 13:24

1 Answers1

0

Before testing this code, you must test the sql query on Microsoft SQL SERVER Management

    <?php 
        $connection_info = array( 
              "Database" => "master", 
              "UID" => "sa", 
              "PWD" => "workingpw" );
        $conn = sqlsrv_connect( "DESKTOP\SQLEXPRESS", $connection_info );
        if ($conn === false) {
           die(print_r(sqlsrv_errors(), true));
        }
        $query = "RESTORE DATABASE [DatabaseName] 
            FROM  DISK = N'/var/opt/mssql/data/Backup.BAK' 
            WITH  FILE = 1,MOVE N'BackupEnergie' TO 
            N'/var/opt/mssql/data/DatabaseName.mdf', 
            MOVE N'BackupEnergie_log' TO 
            N'/var/opt/mssql/data/DatabaseName_log.ldf',  
            NOUNLOAD,  REPLACE,  STATS = 5";
       sqlsrv_configure("WarningsReturnAsErrors", 0);
       if (($stmt = sqlsrv_query($conn, $query))) {
           do {
                print_r(sqlsrv_errors(), true));
              } while (sqlsrv_next_result($stmt));
           echo " * ---End of result --- *\r\n";
           sqlsrv_free_stmt($stmt);
       }
       sqlsrv_configure("WarningsReturnAsErrors", 1);
Naoufel Raouine
  • 1
  • 1