使用CodeCeption（PHP）还原数据库会使数据库处于“恢复”状态

Question

I'm trying to restore a MSSQL database at the start of a run of acceptance tests written using CodeCeption (PHP). For those who know CodeCeption, I'm using the dump file to run the restore.

The restore command I'm using works perfectly when run in SQL Management Studio but when run from the test suite, it stays in the "Restoring" state and is therefore not useable. Running

RESTORE DATABASE [dbname] WITH RECOVERY

at this point will fail with the message

"The database cannot be recovered because the log was not restored" however running the full restore command (RESTORE DATABASE [dbname] FROM DISK....)

in Management Studio at this point will work. I'm using WITH REPLACE, RECOVERY on the restore command.

I've also looked at the following blog post (https://blogs.msdn.microsoft.com/brian_swan/2010/07/01/restoring-a-sql-server-database-from-php/) but the solution posted there doesn't appear to work either. I have also tried to run the restore using combinations of xp_cmdshell and sqlcmd but still without success.

I have theorised that the way that CodeCeption runs the dump file, it will already have a connection to the database open so I have added a

USE [master] 

immediately before the restore and checked the processes in activity monitor to ensure that there are no connections to the database being restored.

I'm convinced this is a problem somewhere in the PHP CodeCeption code which can be seen here - https://github.com/Codeception/Codeception/blob/2.1/src/Codeception/Module/Db.php

Any suggestions?

Answer 1

Codeception simply executes SQL statements from SQL file that you configured with dump parameter: https://github.com/Codeception/Codeception/blob/2.1/src/Codeception/Lib/Driver/Db.php#L105

SqlSrv specific code is in https://github.com/Codeception/Codeception/blob/2.1/src/Codeception/Lib/Driver/SqlSrv.php

If you know how to improve it, you are welcome to contribute.
Please add content of your SQL file to the question.
If it only contains a RESTORE DATABASE statement, then it probably is an issue of pdo_sqlsrv driver.

My only advice is to use SQL statements CREATE TABLE and INSERT to populate your database.