Search code examples
phpsql-serveryiidatabase-backupsdatabase-restore

Yii backup/restore MSSQL database

I have a Yii console application (using Yii v1.1.14) and I am wanting to include database backup and restore command functionality.

For backup functionality, the code is as follows:

$backupDir = "C:\\mssql_backup\\backup.bak";

// Grab the db name from the config values
$dbName = $this->dbConfig['dbMainDatabaseName'];

$cmd = "USE [master] BACKUP DATABASE [$dbName] TO DISK = '$backupDir' WITH FORMAT";

$this->mssql->createCommand($cmd)->execute();

The command executes but the backup is never created. I am not sure where the command is wrong as it executes perfectly fine using SQL Server Management Studio.

For restore functionality, the code is as follows: 

$backupDir = "C:\\mssql_backup\\backup.bak";

// Grab the db name from the config values
$dbName = $this->dbConfig['dbMainDatabaseName'];

$singleUserCmd = "USE [master] ALTER DATABASE [$dbName] SET single_user WITH ROLLBACK IMMEDIATE";
$restoreCmd = "USE [master] RESTORE DATABASE [$dbName] FROM DISK ='$backupDir' WITH REPLACE, RECOVERY";

$this->mssql->createCommand($singleUserCmd)->execute();
$this->mssql->createCommand($restoreCmd)->execute();

The database appears to be restored however, stuck in the recovering state (which the WITH RECOVERY is supposed to handle). I've tried different variations of switching RECOVERY and REPLACE options and even split the commands into 2 commands to execute and the same result is seen. Again, the commands run as expected in SQL Server Management Studio.

It might be worth noting that I also have a command to drop a database:

$singleUserCmd = "USE [master] ALTER DATABASE [$name] SET single_user WITH ROLLBACK IMMEDIATE";
$dropCmd = "USE [master] DROP DATABASE [$name]";

$this->mssql->createCommand($singleUserCmd)->execute();
$this->mssql->createCommand($dropCmd)->execute();

The drop database command works flawlessly using the same connection, user, etc. which makes me think it is probably not a permissions issue.

I'm not sure where to go from here and any suggestions would be greatly appreciated.

Solution

  • This is not a problem with Yii, it's actually a known issue in the PDO Layer in PHP, see SQL Server: Database stuck in “Restoring” state with PHP