MSSQL database stuck in 'restoring" state with PHP

Question

I can execute the following in SSMS and the database will be fully backed up and restored under a new DB name:

USE [master]; BACKUP DATABASE PW_TEMPLATE TO DISK=N'C:\PW_TEMPLATE\PW_TEMPLATE_full.bak'
USE [master]; RESTORE DATABASE PW_TEST_0001 FROM DISK = N'C:\PW_TEMPLATE\PW_TEMPLATE_full.bak' WITH REPLACE, RECOVERY, MOVE 'PW_TEMPLATE' TO 'H:\DATA\PW_TEST_0001.mdf', MOVE 'PW_TEMPLATE_log' TO 'I:\LOGS\PW_TEST_0001_log.ldf'

If I use the same from within PHP, it stays stuck in "restoring" status.

What is the difference between doing it in SSMS or via PHP and how can I fix this issue?

I'm using:

Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 
Sep  7 2018 01:37:51 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

PHP Code:

public function actionDeployDatabase($db_name)
{
    $backup = "USE [master]; BACKUP DATABASE PW_TEMPLATE TO DISK=N'C:\PW_TEMPLATE\PW_TEMPLATE_full.bak'";
    echo $backup . PHP_EOL;
    \Yii::$app->db2->createCommand($backup)->execute();
    $restore = "USE [master]; RESTORE DATABASE __DBNAME__ FROM DISK = N'C:\PW_TEMPLATE\PW_TEMPLATE_full.bak' WITH REPLACE, RECOVERY, MOVE 'PW_TEMPLATE' TO 'H:\DATA\__DBNAME__.mdf', MOVE 'PW_TEMPLATE_log' TO 'I:\LOGS\__DBNAME___log.ldf'";
    $restore = $this->str_replace2('__DBNAME__', $db_name, $restore, -1, $count);
    echo $restore . PHP_EOL;
    \Yii::$app->db2->createCommand($restore)->execute();
    echo "Done..." . PHP_EOL;
}

public function str_replace2($find, $replacement, $subject, $limit = -1, &$count = 0)
{
    $ptn = '/' . preg_quote($find, '/') . '/';
    return preg_replace($ptn, $replacement, $subject, $limit, $count);
}

}

I believe the problem is you need to consume all the results from the restore command (like SSMS does). Try somethting like: `$resultSet = \Yii::$app->db2->createCommand($restore)->query();$resultSet->readAll();$resultSet->nextResult();`. I don't do PHP so I can't provide a tested answer. — Dan Guzman, Apr 04 '21 at 22:24
A similar [Q&A](https://stackoverflow.com/questions/64018598/backup-sql-server-database-using-php/64041766#64041766). A possible solution is the following approach: `$result = \Yii::$app->db2->createCommand($backup); $result->execute(); while ($result->pdoStatement->nextRowset() != null) {};` — Zhorov, Apr 05 '21 at 07:11
And an additional question - what driver do you use to connect to SQL Server? Thanks. — Zhorov, Apr 05 '21 at 11:28

Adi · Accepted Answer · 2021-04-06T19:13:15.963

1

Could you please give what this prints out echo $restore . PHP_EOL; by commenting out the str_replace2() on $restore?

$restore = "USE [master]; RESTORE DATABASE __DBNAME__ FROM DISK = N'C:\PW_TEMPLATE\PW_TEMPLATE_full.bak' WITH REPLACE, RECOVERY, MOVE 'PW_TEMPLATE' TO 'H:\DATA\__DBNAME__.mdf', MOVE 'PW_TEMPLATE_log' TO 'I:\LOGS\__DBNAME___log.ldf'";

This statement is using __DBNAME__ in a static string. Do you wish to use PW_TEST_0001 instead? If so then $restore needs to be dynamic.

Edit: This is a familiar issue with the PDO driver, the problem maybe in the internal drivers upon which the PDO is built. Changing the driver will help resolve the issue.

edited Apr 06 '21 at 19:13

answered Apr 05 '21 at 00:35

Adi

186
9

It prints out exactly what I posted above, the 2nd SQL command – Andreas Hinderberger Apr 05 '21 at 13:36
@AndreasHinderberger Oh I apologize for that but this is a familiar issue with the PDO driver, the problem maybe in the internal drivers upon which the PDO is built. Could you please try a different driver? Reference from some very old issues - https://stackoverflow.com/questions/24190722/yii-backup-restore-mssql-database https://stackoverflow.com/questions/9050799/sql-server-database-stuck-in-restoring-state-with-php – Adi Apr 05 '21 at 18:23
Thanks. I haven't come over this issue yet during my google search. Please make it an answer as changing the driver (on a windows server, drivers installed with webtools) resolves the issue. – Andreas Hinderberger Apr 06 '21 at 12:58
@AndreasHinderberger does the edit looks good for an answer? is that what you were trying to say? – Adi Apr 06 '21 at 19:14
Thanks @Adi. Answer accepted and bounty awarded :D – Andreas Hinderberger Apr 07 '21 at 14:34
I ended up scrapping the whole PHP part of the deployment framework and implemented everything into a Windows Service. Problem solved, so I won't have to worry about different IIS PHP setups down the road – Andreas Hinderberger Apr 07 '21 at 14:35

MSSQL database stuck in 'restoring" state with PHP

1 Answers1