Skip to content

Backup and restoring mssql database with php

January 31, 2012

Requirement:

mssql sql driver for php enable. In my case I use php_sqlsrv_52_ts_vc6.dll from microsoft with php 5.2 thread safe enabled. for more information for a specific version visit http://sqlsrvphp.codeplex.com/. I don’t use mssql and pdo_mssql driver that comes by default with php 5.2.

Note with bug

The pdo versions(both from php_mssql and php_pdo_sqlsrv) just don’t work. I have tried it.  When restoring database, I ran into a bug with mssql server stack in restoring state.

Code

//ch_debug.php
 <?php
define('CH_TEST', 0) ; // possible value 0,1
define("CH_DEBUG",1) ;
function ch_debug($var,$exit=false,$configable=true){
    $templates[] = <<<EOT
        <div style='text-align:left;border-top:1px solid #ccc;background-color:white;color:black;overflow:auto;' >
            <pre>
                <br /> <strong> line : </strong> {line}
                <br /> <strong> file : </strong> {file}
                <br /> {data}
            </pre>
        </div>
EOT;

    $templates[]= <<<EOT
        \n
        -------------------------------debug ---------------------------
        line : {line}, file : {file}
        output: ->
        {data}
        ----------------------------------------------------------------
        \n\r
EOT;

    if(CH_DEBUG || $exit==true){
        $debug_traces = debug_backtrace();
        $debug_trace=$debug_traces[0];

        $str = strtr($templates[CH_TEST],
            array( "{line}"=>"{$debug_trace['line']}",
                    "{file}" => "{$debug_trace['file']}",
                    "{data}"=> print_r($var, true) //debug_trace['args'][0]
                 ));
        echo $str ;
        if($exit==true)
            exit;
    }
}
//config.php
<?php

    $connOptions = array("Database"=>"master", "UID"=>"sa", "PWD"=>"123456");
    $conn = sqlsrv_connect("WORK-MSSQL", $connOptions); 

    sqlsrv_configure( "WarningsReturnAsErrors", 0 );
    $backup_file = "c:\\backup\\test.bak" ;
 //backup.php
<?php
    require 'ch_debug.php';
    require 'config.php';
    $log_backup_file= "c:\\backup\\last-test-log.bak" ;

    $sql = "BACKUP DATABASE test TO DISK = '$backup_file' ";

    ch_debug($sql);

    $stmt = sqlsrv_query($conn, $sql);

    if($stmt === false)
        ch_debug(sqlsrv_errors());

    else
        ch_debug("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";
    ch_debug($sql);
    $stmt = sqlsrv_query($conn, $sql);
    if($stmt === false)
        ch_debug(print_r(sqlsrv_errors()));
    else
        ch_debug( "Transaction log backed up to $log_backup_file");

//restore.php
<?php

    require 'ch_debug.php';
    require 'config.php';

    $sql = "RESTORE DATABASE test FROM DISK = '$backup_file' WITH RECOVERY";
    ch_debug($sql);
    $stmt = sqlsrv_query($conn, $sql);
    if($stmt === false)
        ch_debug(print_r(sqlsrv_errors()));
    else
        ch_debug( "Database restored from $backup_file</br>" );

    //Put DB into usable state.
    $sql = "USE test";
    ch_debug($sql);

    $stmt = sqlsrv_query($conn, $sql);

    if($stmt === false)
        ch_debug(print_r(sqlsrv_errors()));
    else
        ch_debug("Using TestDB</br>");

For more information visit http://blogs.msdn.com/b/brian_swan/archive/2010/07/01/restoring-a-sql-server-database-from-php.aspx

Advertisements

From → Php

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: