Freigeben über

BACKUP and RESTORE A Database with the SQL Server Driver for PHP

  • Artikel

In this post I’ll look at how to use the SQL Server Driver for PHP to backup and restore a database. It is important to understand that there are many ways to backup and restore a database. The strategy you choose really depends on your application and your data. For a non-critical application with minimal data turnover, some data loss might be OK. In this case, a full backup done on a daily or weekly basis might be just fine. On the other hand, if you have a mission critical application with lots of highly sensitive data, you might want to go to extremes to ensure that no data is lost. This could mean doing full backups on a daily basis with periodic differential backups and very frequent log backups. Since I can’t possibly cover everything between those extremes in one post, I’ll aim for something in the middle. Like a blog, perhaps. In other words, I’ll address the scenario in which I don’t want to lose data, but I’m not willing to go to extremes to preserve it. If I encounter a catastrophic failure or someone accidentally deletes some data, I won’t lose the vast majority of my data, but I might lose some transactions that occurred just before the failure.

Oh, and one more caveat before I start. All the backup and restore operations I’ll perform will be done with the SQL commands BACKUP and RESTORE. So, this isn’t necessarily a PHP-specific post. The techniques here (and specifically the SQL) can be applied regardless of the language you use to interact with SQL Server Express.

 

Overview

The basic process for backing up a database involves doing the following:

  1. Creating a full (as opposed to a differential) database backup. This essentially creates a copy of your database.
  2. Create periodic log backups. These capture activity since the last backup.

Now, if some sort of failure or user error occurs, you can then restore the database by doing the following:

  1. Restore the database using the last full backup. Doing only this, however, will lose any transactions that have occurred since the last backup.
  2. Restore the log backups since the last full database backup. This is similar to “replaying” the transactions that have occurred since the last backup.

The diagram in this MSDN topic, Performing a Complete Database Restore (Full Recovery Model), does a good job of capturing the process I’ll use in this post.

 

Creating a Full Backup

The first thing you need to do is to make sure that the recovery model of your database is set to FULL. (See Recovery Model Overview for more information.) You can do this by executing the following script (but ideally this would be done at the time the database is created):

$connOptions = array("Database"=>"master", "UID"=>$uid, "PWD"=>$pwd);
$conn = sqlsrv_connect($serverName, $connOptions);

$sql = 'ALTER DATABASE [TestDB] SET RECOVERY FULL';
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Recovery model set to FULL";
}

Note: Before proceeding, you need to turn off the WarningsReturnAsErrors setting of the SQL Server Driver for PHP. You can do this by adding sqlsrv.WarningsReturnAsErrors=0 to the [sqlsrv] section of your php.ini file and then restart your Web server. This is necessary because SQL Server returns messages (which are, by default, handled as errors by the driver) upon successful backup and restore operations.

Now you can run another script to create a backup file, TestDB_Backup.bak. In many situations, this process would be automated, but there are scenarios in which you might want to do this manually:

$backup_file = "C:\Backups\TestDB_Backup.bak";
$sql = "BACKUP DATABASE TestDB TO DISK = '".$backup_file."'";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Database backed up to $backup_file";
}

Note that I’m creating the backup file on the local disk, which isn’t necessarily a best practice. In a production environment, you would likely want to write the back up file to a different machine or separate disk drive.

 

Creating Log Backups

Suppose you create a full database backup every night at midnight. Then, to capture any transactions that occur between backups, you need to backup your transaction log periodically. Again, a simple script does this. And, again, this process might be automated:

$log_backup_file = "C:\Backups\TestDB_Log_Backup.bak";
$sql = "BACKUP LOG TestDB TO DISK = '".$log_backup_file."'";
$stmt = sqlsrv_query($conn, $sql);
if($stmt === false)
{
die(print_r(sqlsrv_errors()));
}
else
{
echo "Transaction log backed up to $log_backup_file";
}

Note that you must track the number of times you backup the log. This will be important when you want to restore the database.

 

Restoring a Database

Now let’s suppose that some failure occurs, or that a user accidentally deletes important data. If that happens, you probably won’t be restoring the database by writing PHP code. So, I’ll assume you are using SQL Server Management Studio (which can be downloaded here), or some other tool that allows you to execute SQL directly. (Also note that backups can be used to copy a database to another location. See Copying Databases with Backup and Restore for more information.)

The first thing we want to do is see if we can capture any transactions since the last log backup (this is called “capturing the tail of the log”):

BACKUP LOG TestDB TO DISK = 'C:\Backups\TestDB_Log_Backup.bak' WITH NORECOVERY

This also puts the database into the “restoring” state.

Now we restore that last full database backup. Again, the script is similar to the others, the the SQL is different:

RESTORE DATABASE TestDB FROM DISK = 'C:\Backups\TestDB_Backup.bak' WITH NORECOVERY

If we altered the SQL so that the database was restored “WITH RECOVERY”, it would be in a useable state, but all transactions since the full backup would be lost. As it is now, the database is still in the “restoring” state. The next step is to “replay” the transaction logs, and this is where keeping track of the number of log backups is important. We need to issue a RESTORE LOG command for each log backup that was done (not counting the tail log backup):

RESTORE LOG TestDB FROM DISK = 'C:\Backups\TestDB_Log_Backup.bak' WITH FILE=<backup number>, NORECOVERY

So, for example, if I had done two log backups (not counting the tail log backup), I would execute the following:

RESTORE LOG TestDB FROM DISK = 'C:\Backups\TestDB_Log_Backup.bak' WITH FILE=1, NORECOVERY
RESTORE LOG TestDB FROM DISK = 'C:\Backups\TestDB_Log_Backup.bak' WITH FILE=2, NORECOVERY

Finally, we restore the tail of the log and put the database back into a useable state:

RESTORE LOG TestDB FROM DISK = 'C:\Backups\TestDB_Log_Backup.bak' WITH FILE=<backup number>, RECOVERY

Continuing with my example of two file log backups, this would be my last step in restoring the database:

RESTORE LOG TestDB FROM DISK = 'C:\Backups\TestDB_Log_Backup.bak' WITH FILE=3, RECOVERY

By no means to I expect this post to address all realistic backup and restore scenarios, but I hope it gives you some idea of how to do backups and restores. Of course, it’s not so easy when you get into the details of real restore scenarios…so I’d like to hear how you do (or would like to do) backup and restores. With a few real-world scenarios in hand, perhaps I can write a post that is more specific.

Thanks.

-Brian

Share this on Twitter

Comments

  • Anonymous
    April 29, 2010
    Thanks Bryan for your post,I'am starting with MsSql, for restoring MySQl databases there are several snippets, classes  available, that works  as PHPMyadmin but for  SQLServer i didn't found nothing yet.The only option is to use SQL Server Management Studio to restore an database?, i want to run an script to restore my new database.Thank you
  • Anonymous
    April 30, 2010
    It sounds like you are running into the same problem that I describe in this post: http://blogs.msdn.com/brian_swan/archive/2010/04/13/working-down-the-stack-enabling-odbc-tracing-with-the-sql-server-driver-for-php.aspx. It looks like there is a bug in the sqlsrv driver that makes it impossible to RESTORE a database from a script. I will ping the dev team again and find out the status of that bug.So, in the meantime, yes - I think you'll have to use Management Studio to restore a DB. You can download free versions here...2008: http://www.microsoft.com/downloads/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en2005: http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=enHope that helps.-Brian
  • Anonymous
    June 18, 2012
    Brian, this blog page has been so helpful; thanks. All of my restore scripts (using PHP drivers sql server 2012 3.0.1) work except one.  The following command runs in sql server management studio and behaves as expected:RESTORE DATABASE [thm] FROM  DISK = N'C:UsersPublicthmbackup2012-06-18_thm.bak' WITH  RECOVERYThe following command, when issued with sqlsrv_query() from my php script runs with a non-false return, but seems to leave the database unuseable:RESTORE DATABASE [thm] FROM DISK = N'C:UsersPublicthmbackup2012-06-18_thm.bak' WITH RECOVERYThe backup was run from sqlsrv_query() using the command:BACKUP DATABASE [thm] TO DISK = 'C:UsersPublicthmbackup2012-06-18_thm.bak' WITH INITWhat can/should I do differently?Thanks,Jeff
  • Anonymous
    June 19, 2012
    @Jeff: Does this post help solve your problem? blogs.msdn.com/.../restoring-a-sql-server-database-from-php.aspx-Brian
  • Anonymous
    July 15, 2014
    This query runs successfully via php, but the produced bak file is not proper one does' nt contain any data, it throws the error when try to import via sql server management studio.