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

Share this on Twitter

Comments

• Anonymous
  June 09, 2015
  Though a rather dated article, it seems to be the main source to be referred to when looking for a solution for exactly the problem you described. When I try to put the DB in a usable state, I receive the error that I can't do that as the database is still restoring... Any ideas? Thanks!

• Anonymous
  January 26, 2017
  The comment has been removed