Home > Code Snippets > php MsSQL Backup

php MsSQL Backup

January 30th, 2010 Leave a comment Go to comments

A php script to dump database as sql from MS SQL

We started porting (provide dual support) one of our application from MySQL to MSSQL, sorry, actually we were trying to run the same code base on either MSSQL or MySQL as the client wishes. And those who knows both, will agree to the level of toughness and agony when considers the fact that all of us are on MySQL and are new to MSSQL. In this context, we wanted to provide backup and restore to the application administrator through the system itself, and was not contented with the .bak files provided by MSSQL backup.

The above led to an experiment to try to code a php based database export as sql, with optional zip compression (and graceful degaradation). The outcome is published herewith with a clean intension. And there are a couple of drawbacks, like we have never used the foreign keys and constraints. Hence the backup tool does not address any fk_constratints.

I would appreceate any comments from MSSQL experts and veterans regarding the left out portions in the backup tool. Together I expect the tool to be made into a more useful one. The script in native php is attached here with. For this to run smoothly in ubuntu linux (my favorite distro), the php5-sybase package should be installed. Tweak the configuration part, as given in the example implementation at the bottom.

I would appreciate any one who is downloading and using this to make a comment and reference to how you used this script. Just for the sake of auditing.

PHP MSSQL Backup (1794)

Note: While moving along, I have found that the dump which my script generates is just bare minimum and will be of use only to a very small and poorly designed database. I am working on enhancing the script with all the essentialities. A release date for the extended version cannot be noted now. (added Mar 1, 2010)

  1. High School
    February 5th, 2010 at 02:29 | #1

    Great article . Will definitely apply it to my blog.Thanks.

  2. Undisclosed
    February 7th, 2010 at 07:09 | #2

    he used the same technique in the end and it worked

  3. lkaiman
    February 19th, 2010 at 19:13 | #3

    hi,

    …scuse me for my english…

    how do you proceed for make a backup with a data type “timestamp” ?

    It is in this part:
    function buildInsertQuery($a, $table, $struct){

    // do some value escaping
    foreach($a as $field => $value){
    switch($struct[$field]['type']){
    case ‘varchar’:
    case ‘nchar’:
    case ‘char’:
    case ‘text’:
    case ‘datetime’:
    if(!is_null($value)) $a[$field] = “‘” . str_replace(“‘”,”””, $value) . “‘”;
    elseif($struct[$field]['null'] == ‘NO’) $a[$field] = “””;
    else $a[$field] = “NULL”;
    break;
    case ‘timestamp’: $a[$field]=’?????????????’; break;
    case ‘numeric’:
    case ‘int’:
    $a[$field] = sprintf(“%d”, $value);
    break;
    }
    }

    return ‘INSERT INTO ['.$table.'] (['.implode('],[', array_keys($a)).']) VALUES (‘.implode(‘,’,array_values($a)).’);’;
    }

  4. February 19th, 2010 at 22:47 | #4

    @lkaiman
    I am not sure, you asking for backup of MySQL or MSSQL ?, if it is MySQL you dont need to bother about creating the INSERT statements for backups, better think about writing csv output.

  5. peterm
    January 13th, 2011 at 00:08 | #5

    nice script. it seemed to work for my purposes. one thing you should do is surround your table names with square brackets to avoid keyword conflicts. i had to manually update the output file to put the brackets.

  6. Kiran
    March 30th, 2012 at 18:55 | #6

    Actually the code which you are given is not working properly, i have given my credintials of database and when i run the script it is generating a sql and creating only procedures not the tabales and data. And showing the following error in the web page..
    Warning: mssql_select_db() [function.mssql-select-db]: message: Could not locate entry in sysdatabases for database ‘Kiran’. No entry found with that name. Make sure that the name is entered correctly. (severity 16) in C:\xampp\htdocs\test\php_mssql_backup.php on line 55

    Warning: mssql_select_db() [function.mssql-select-db]: Unable to select database: Kiran in C:\xampp\htdocs\test\php_mssql_backup.php on line 55

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\test\php_mssql_backup.php on line

    I have given proper database name and proper credentials. like

    $config_default = array(‘host’ => ‘myhostname’, ‘user’ => ‘myusername’, ‘pass’ => ‘mypassord’, ‘db’ => ‘mydbname’, ‘compress’ => false);

    Please Help me..

    Thanks Inadvance

  1. No trackbacks yet.