SQL Server 2005 Dump to SQL statements

Flattr the SQL Server 2005 Dump to SQL statements post

Say you have a development environment setup using SQL Server 2005 Express Edition and your customer has a SQL Server 2000 database accessible only via ODBC and you can only run a DB import via a script. How would you do it? Easy you might think just run msdbdump.exe on the command line, well this isn’t MySQL so you are not so fortunate.

Backing up your DB is easy with SQL Server using the following commands:

sp_addumpdevice 'disk', 'pseudoName', 'C:yourFilename.mdb';
GO
DUMP DATABASE yourDatabaseName TO pseudoName;
GO
sp_dropdevice pseudoName;

But this just gives you a binary file that can be restored if you have access privileges to the live database for restoring. If you are in a shared hosting environment or one where the paranoid admins won’t give you remote desktop access and the only access you have is to run a PHP script to import the data and schema via SQL you will need to export the DB to T-SQL format. For more information on T-SQL I recommend the following two books Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes and Microsoft SQL Server T-SQL Fundamentals.

Microsoft have a little program to perform this very function: Microsoft SQL Server Database Publishing Wizard 1.1 It is difficult to find on the web so I aim to save you the time I spent hunting for it. When you run the wizard make sure to set:

  • Drop existing objects in script to false
  • Schema qualify to false
  • Script for target database to SQL Server 2000

It does seem to chew on the cud for quite some time so grab a beverage.

Now for the PHP portion of the process. So you have uploaded your lovely T-SQL dump file to a PHP accessible location on your webserver and now you are wondering how to get into your DB via ODBC. Well you will need a PHP script like the one I have supplied below.

A couple of the complexities to be aware of before you continue. It seems that the T-SQL dump file comes out as UTF16 and we need it in UTF8 so you will need to convert it to UTF8 before you can import. I used a neat little function available from Modular.org for this purpose. This may or may not meet your needs. If you need a more accurate conversion method then I recommend you start your search with the PHP module/function mbstring . T-SQL contains reference and keywords that ODBC/MS SQL cannot understand. I have included some regex to strip these out.

My script is by no means perfect or factored down so feel free to make suggestions or improvements.

<?php
/**
 * Import a MS SQL T-SQL dump file into
 * and ODBC connection via a PHP browser
 * script.
 *
 * I have not tested the script with binary DB fields so
 * I cannot confirm whether it works or not.
 *
 * @author Simon Holywell
 * @version 8/4/2008
 */

//Up the execution limits - you may need to tweak for
//larger database imports I have only tested up to 9MB
//which took 30seconds to process with this file.
ini_set('max_execution_time', 1000);
ini_set('max_input_time', 1000);
ini_set('memory_limit', '120M');

//ODBC connection details
$server_dsn = 'dsn';
$user = 'user';
$pass = 'pass';

//path to T-SQL dump file
$dump_filename = '../dump.sql';

//drop the tables that already
//exist in the DB?
$drop_prexisting = true;

//How many queries should the script
//execute per database connection
//this is in place to stop the DB/ODBC
//connection from timing out on us.
//5 is a safe number if you have large
//database fields
$how_many_per_connection = 5;

function utf16_to_utf8($str) {
    //http://www.moddular.org/log/utf16-to-utf8
    //16th March 2006
    $c0 = ord($str[0]);
    $c1 = ord($str[1]);

    if ($c0 == 0xFE && $c1 == 0xFF) {
        $be = true;
    } else if ($c0 == 0xFF && $c1 == 0xFE) {
        $be = false;
    } else {
        return $str;
    }

    $str = substr($str, 2);
    $len = strlen($str);
    $dec = '';
    for ($i = 0; $i < $len; $i += 2) {
        $c = ($be) ? ord($str[$i]) << 8 | ord($str[$i + 1]) :
                ord($str[$i + 1]) << 8 | ord($str[$i]);
        if ($c >= 0x0001 && $c <= 0x007F) {
            $dec .= chr($c);
        } else if ($c > 0x07FF) {
            $dec .= chr(0xE0 | (($c >> 12) & 0x0F));
            $dec .= chr(0x80 | (($c >>  6) & 0x3F));
            $dec .= chr(0x80 | (($c >>  0) & 0x3F));
        } else {
            $dec .= chr(0xC0 | (($c >>  6) & 0x1F));
            $dec .= chr(0x80 | (($c >>  0) & 0x3F));
        }
    }
    return $dec;
}

function microtime_float() {
    //http://www.developertutorials.com/blog/php/php-measure-max-execution-time-script-execution-time-83/
    //Akash Mehta
    //March 15th, 2008
    list($utime, $time) = explode(" ", microtime());
    return ((float)$utime + (float)$time);
}
?>
<p>Dropping:</p>
<?php
//drop all pre-existing tables in the DB prior to importing
if($drop_prexisting)
    $table_drop_sql = '';
$link2 = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
$tables = odbc_tables($link2);
while (odbc_fetch_row($tables)){
    if(odbc_result($tables,"TABLE_TYPE")=="TABLE") {
        $table_name = odbc_result($tables,"TABLE_NAME");
        print $table_name.'<br />';
        if($drop_prexisting)
            $table_drop_sql .= "DROP TABLE $table_name\n";
    }
}
odbc_close($link2);

if($drop_prexisting) {
    $link2 = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
    @odbc_exec($link2, $table_drop_sql);
    odbc_close($link2);
}
?>
<p>Have patience - converting the DB file to UTF8 and removing guff&hellip;</p>
<?php
$script_start = microtime_float();

$file = file_get_contents($dump_filename);
//convert from utf16 to utf8 character encoding
$file = utf16_to_utf8($file);
//strip out errant MS line endings
$file = str_replace(array("\r","\r\n","\r\n","\n\n"), "\n", $file);
//strip more guff that ODBC/MS SQL cannot understand
//(the dbo object doesn't exist via ODBC)
$file = preg_replace("/^IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[[a-z_]+]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)$/im",'',$file);
//strip out print statements
$file = preg_replace("/^print '[a-z0-9 ]+'$/im", '', $file);
//strip SQL Server comments
$file = preg_replace('/^/[*]{6} [a-z0-9[] /:._]+ [*]{6}/$/im', '', $file);
//strip out SQL Server rubbish and any errant dbo refs
$file = str_replace(array("GO\n",'[dbo].','dbo.'), '', $file);

//lets split up the queries by stepping through the file and breaking on insert statements
$search_string = "\nINSERT [";
$file_length = strlen($file);
$iteration = 0;
$total_interations = 0;
$total_exe_time = bcsub(microtime_float(), $script_start, 4);
$queries = array();
while(true) {
    $script_start = microtime_float();
    $end_yank = strpos($file, $search_string, strlen($search_string));
    //store the query(s)
    $queries[] = substr($file, 0, $end_yank);
    //scrub the saved query(s) from the main T-SQL
    $file = substr($file, $end_yank);

    if(empty($file) or
       strlen($file) <= strlen($search_string) or
       $end_yank == 0)
        break;

    if(1 == (++$iteration / $how_many_per_connection)) {
        //process our accumulated queries
        $link = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
        odbc_exec($link, implode("n", $queries)) or die('Could not process SQL: '.odbc_errormsg($link));
        odbc_close($link);
        $queries = array();
        $total_interations += $iteration;
        $script_end = microtime_float();
        $portion_time = bcsub($script_end, $script_start, 4);
        $total_exe_time += $portion_time;
        print '<p>'.$total_interations.' records processed.  Portion: '.$portion_time.' Total: '.$total_exe_time.'</p>';
        $iteration = 0;
    }
}
?>
<p>Successfully Imported:</p>
<?php
$link2 = odbc_connect($server_dsn, $user, $pass) or die('Unable to connect to the server: '.odbc_errormsg());
$tables = odbc_tables($link2);
while (odbc_fetch_row($tables)){
    if(odbc_result($tables,"TABLE_TYPE")=="TABLE")
        echo"<br>".odbc_result($tables,"TABLE_NAME");
}
odbc_close($link);
?>