I have a Yii console application (using Yii v1.1.14) and I am wanting to include database backup and restore command functionality for MSSQL.
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.
