Restoring a SQL Server Database from PHP
A while back I wrote a post that described how to backup a database from PHP. However, I ran into a bug when trying to restore a database from PHP, so I resorted to using SQL Server Management Studio (SSMS) to execute the RESTORE statement. Since then, the SQL Server Driver for PHP team has investigated the bug (which I describe in more detail in this post) and has provided a workaround…which is the subject of this post. The team has also determined that the bug is not in the SQL Server Driver for PHP, but may be in the ODBC/SNAC layer on which the driver is built (more about the driver architecture here).
First, a quick re-cap of the problem: Backing up a database and the log file is simple – you just need to execute BACKUP statements (which is described in more detail here):
BACKUP DATABASE <database_name> TO DISK = <file_name>
and
BACKUP LOG <database_name> TO DISK = <file_name>
Creating backups by executing these statements from PHP worked fine. The problem arose when trying to restore a database by executing a statement like this one from PHP:
RESTORE DATABASE <database_name> FROM DISK = <file_name> WITH RECOVERY
The statement executed without error, but it left the database in the “restoring” state, and therefore unusable.
The fix for this problem is to simple execute a “USE <database_name>” statement after the RESTORE statement. Here is a script that creates a backup, restores the database, and makes it usable by executing the USE statement at the end (note that I turn off the WarningsReturnAsErrors setting, otherwise database messages are treated as errors):
sqlsrv_configure( "WarningsReturnAsErrors", 0 );
$connOptions = array("Database"=>"master");
//Backup database
$sql = "BACKUP DATABASE TestDB TO DISK = '".$backup_file."'";
echo $sql."<br>";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Database backed up to $backup_file<br>";
}//Backup log. Put DB into "Restoring..." state.
$sql = "BACKUP LOG TestDB TO DISK = '".$log_backup_file."' WITH NORECOVERY";
echo $sql."<br>";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Transaction log backed up to $log_backup_file";
}//Restore DB.
$sql = "RESTORE DATABASE TestDB FROM DISK = '".$backup_file."' WITH RECOVERY";
echo $sql."<br>";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Database restored from $backup_file</br>";
}//Put DB into usable state.
$sql = "USE TestDB";
echo $sql."<br>";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Using TestDB</br>";
}
A fairly easy workaround (I’ll see about getting to the root cause), but you might be asking why I (or you) would be interested backing up and restoring a database from PHP? I will hopefully have a good answer for you when I write that WordPress plug-in that does automatic SQL Server backups (now that WordPress runs on SQL Server) and then lets you restore a database from the WordPress management UI.
Thanks.
-Brian