MSSQL database stuck in restoring state with PHP

Question

I am trying to restore a database to a different server through PHP. I managed to execute all needed commands but the database keeps hanging in the 'Restoring...' state on the server.

I have searched and followed the answer on SQL Server: Database stuck in “Restoring” state with PHP (which leads to this article) but this did not work for me; I get an error when trying to change the environment to the newly recovered database.

Code:

sqlsrv_configure( "WarningsReturnAsErrors", 0 );
$connOptions = ["Database"=>"master"];
$sqlConnection = sqlsrv_connect("server_name\\SERVER", $connOptions);

sqlsrv_query($sqlConnection, "USE master");

$sql = "IF EXISTS(SELECT name FROM sys.databases
  WHERE name = 'db_name')
  DROP DATABASE db_name";
sqlsrv_query($sqlConnection, $sql);

$sql = "RESTORE FILELISTONLY FROM DISK='$path'";
$logicalNamesStatement = sqlsrv_query($sqlConnection, $sql);

$moveArray = [];
while($logicalNames = sqlsrv_fetch_array($logicalNamesStatement, SQLSRV_FETCH_ASSOC)){
    if($logicalNames['Type'] === "D"){
        $moveArray['MDF'] = $logicalNames['LogicalName'];
    }
    elseif($logicalNames['Type'] === "L"){
        $moveArray['LDF'] = $logicalNames['LogicalName'];
    }
}

$localDbPath = "c:\\Program Files\\Microsoft SQL Server\\MSSQL11.SERVER\\MSSQL\\DATA\\";

$sql = "RESTORE DATABASE db_name FROM DISK='$path'
    WITH
        MOVE '" . $moveArray['MDF'] . "' TO '" . $localDbPath . "db_name.mdf',
        MOVE '" . $moveArray['LDF'] . "' TO '" . $localDbPath . "db_name_log.ldf',
        REPLACE,
        STATS=10";

sqlsrv_query($sqlConnection, $sql);

$sql = "RESTORE DATABASE db_name FROM DISK='$path' WITH REPLACE, RECOVERY";
sqlsrv_query($sqlConnection, $sql);

sqlsrv_query($sqlConnection, "USE db_name");

The error that comes back is:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Database 'db_name' cannot be opened. It is in the middle of a restore.

Do you have any ideas on how I can solve this? The only authority on a workaround for the bug described in my previously linked article seems to be that exact article, which does not work as you can see.

Thanks!

I have found a workaround by using a command shell execution through PHP. However, I'd still like to get the code above to work. — Bert Meuzelaar, Jul 06 '15 at 12:37
Two things: 1) Have you just printed out the sql commands you're generating to a console and copy/pasted to a query window and tried running them manually? 2) I don't understand the point of running the two RESTORE commands. It would seem that you need one or the other but not both (depending on if you want to move the files or not). — Mitch Schroeter, Jul 06 '15 at 16:43

score 0 · Answer 1 · answered Jul 06 '15 at 20:21

I don't have 50 reputation yet, so feel free to move this to the comments section. I don't see your database names including brackets "[ ]". If you have any database names with spaces or special characters, you'll need the brackets to identify the object properly. And as Mitch already stated, based on what you are doing, there is no reason for two restore operations; just add the RECOVERY option into the first and be done with it.

MSSQL database stuck in restoring state with PHP

1 Answers1