Yii备份/还原MSSQL数据库 [英] Yii backup/restore MSSQL database

问题描述

我有一个Yii控制台应用程序(使用Yii v1.1.14)，并且我想包含数据库备份和还原命令功能.

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();

该命令已执行，但从未创建备份.我不确定该命令在哪里出错，因为使用SQL Server Management Studio可以很好地执行该命令.

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();

但是，数据库似乎已还原，停留在恢复状态( WITH RECOVERY 应该处理).我尝试了切换 RECOVERY 和 REPLACE 选项的不同变体，甚至将命令分成2个要执行的命令，并且看到了相同的结果.同样，命令将在SQL Server Management Studio中按预期运行.

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();

drop database命令使用相同的连接，用户等也可以正常工作，这使我认为这可能不是权限问题.

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.

推荐答案

这不是Yii的问题，它实际上是PHP的PDO层中的已知问题，请参见

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

这篇关于Yii备份/还原MSSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！