用于还原SQL Server数据库的脚本失败

Question

I'm trying to restore a SQL Server database using a PHP script and it keeps failing. I'm on PHP 7.0.4 NTS x86. Since my code was running inside a framework, I decided to isolate and ran code that I found here (which has also been pointed out in another answer on StackOverflow): https://blogs.msdn.microsoft.com/brian_swan/2010/07/01/restoring-a-sql-server-database-from-php/

The code is as follows:

// Set error mode to make sure errors throw exceptions
sqlsrv_configure('WarningsReturnAsErrors', 1);

// Try to connect
$conn = sqlsrv_connect(
    'localhost',
    ['Database'=>'master', 'UID'=>'sa', 'PWD'=>'mypassword', 'ReturnDatesAsStrings'=>true, 'CharacterSet'=>'UTF-8']
);

$sql = "RESTORE DATABASE MYDB FROM DISK='c:\\MYDB.bak' WITH RECOVERY";
echo $sql."<br>"; 
$stmt = sqlsrv_query($conn, $sql); 
if($stmt === false) 
{ 
    die(print_r(sqlsrv_errors())); 
} 
else 
{ 
    echo "Database restored</br>"; 
}

//Put DB into usable state. 
$sql = "USE MYDB"; 
echo $sql."<br>"; 
$stmt = sqlsrv_query($conn, $sql); 
if($stmt === false) 
{ 
    die(print_r(sqlsrv_errors())); 
} 
else 
{ 
    echo "Using MYDB</br>"; 
}

This is the output, the first $stmt === false is true and it prints the error:

RESTORE DATABASE MYDB FROM DISK='c:\MYDB.bak' WITH RECOVERY
Array
(
    [0] => Array
        (
            [0] => 01000
            [SQLSTATE] => 01000
            [1] => 4035
            [code] => 4035
            [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Processed 5496 pages for database 'MYDB', file 'DB_Data' on file 1.
            [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Processed 5496 pages for database 'MYDB', file 'DB_Data' on file 1.
        )

)
1

The DB gets created but just appears in Restoring mode. If I try to run the same command from SSMS using the same user, it works fine. Another interesting thing is if I look at the SQL Server log, I see this after every attempt:

2017-03-10 09:33:12.37 spid57      The database 'MYDB' is marked RESTORING and is in a state that does not allow recovery to be run.
2017-03-10 09:33:34.76 spid57      Starting up database 'AdventureWorks2008R2'.
2017-03-10 09:33:34.80 spid57      Starting up database 'ReportServer'.
2017-03-10 09:33:34.83 spid57      Starting up database 'ReportServerTempDB'.

Not sure why it's starting up those same 3 databases every time after the restore is attempted.

I also tried doing the restore using PDO, same result, a database stuck in Restoring mode.

Answer 1

SQL Server prints warnings and errors pretty much the same way-- just with a different severity level so the client software can know the difference and take appropriate action.

I don't know PHP, so I'm not sure what you are trying to accomplish with the line

sqlsrv_configure('WarningsReturnAsErrors', 1);

But from the sounds of it, that is making the lower severity "warning" messages from SQL Server get treated as actual errors by your client code. Set that to 0 and I'm guessing your script will continue past the restore database messages and treat them as informational rather than exceptions.