current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
2

I had created a MySQL database dump in php successfully using this below code.

$dbuser='root'; 
$dbname='Student';
$dbtable='StudentDetails'; 
$dbhost='localhost'; 
$dbpass='root'; 
$name="Student_mysql"; 
exec("mysqldump --user=$dbuser --password=$dbpass --host=$dbhost $dbname $dbtable > C:/xampp/htdocs/utils/".$name.".sql");

I would like to create a dump of a MS SQL Server database using SQLSRV & PHP Code

I have no idea on it
please see the below image this my DataBase and StudentDetails-table fields

MY DB Structure enter image description here

share

migration rejected from stackoverflow.com Jan 6 '14 at 15:16

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Mark Storey-Smith, RolandoMySQLDBA, Paul White, ypercube, Marian Jan 6 '14 at 15:16

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1  
msdn.microsoft.com/en-us/library/ms186865.aspx –  a_horse_with_no_name Jan 4 '14 at 12:55
    
Can you clarify whether you want to backup the whole database, or just one table? The solution in each scenario is quite different. There is no simple equivalent of the mysqldump utility. –  Iain Elder Jan 5 '14 at 19:19

3 Answers 3

up vote 0 down vote accepted

Use this PHP code to take Backup 

<?php
    $query = "BACKUP DATABASE Student TO DISK = 'D:\cdbname.bak'";

    $conn = sqlsrv_connect('SERVER\SQLEXPRESS',array('UID'=>'username', 'PWD'=>'password','Database'=>'Student'));

    if ( !$conn )
    {
        print_r(sqlsrv_errors());
        exit;
    }

    sqlsrv_configure("WarningsReturnAsErrors", 0);
    if ( ($stmt = sqlsrv_query($conn, $query)) )
    {
        do 
        {
            /*print_r(sqlsrv_errors());
            echo " * ---End of result --- *\r\n";*/
        } while ( sqlsrv_next_result($stmt) ) ;
        sqlsrv_free_stmt($stmt);
    }
    sqlsrv_configure("WarningsReturnAsErrors", 1);
    sqlsrv_close($conn);
    ?>
share
    
let me try it out.. –  faroke moahmed Jan 6 '14 at 7:00
up vote 1 down vote

MS SQL handles backups differently from mySQL. Rather than dumping the data and the definitions, it creates a binary description of the data and how it's laid out on the disk. It also creates recovery checkpoints and other features for better restoring.

In order to get the type of data you're looking for, you'll need to use the generate scripts feature in SSMS. There's an option for types of data to script.

I'm not aware of any way to do it from PHP.

share
    
At a simple level, the backup process is the same. Each backup process creates a file with a static copy of the database structure and data. You can use this file to restore the database at another time or in another place. The main basic difference is you can't open a SQL Server backup in Notepad and understand the content. With PHP's exec and sqlcmd on the path you can do anything you could do in T-SQL. –  Iain Elder Jan 5 '14 at 19:22
    
Perhaps I misunderstand. My impression was that the OP wanted the MS SQL equivalent of mysqldump. Depending on what he intends to do with the file, that may or may not be what he's looking for. Of course he can run "Backup database mydb" through PHP, but if he wants a text file that looks like the output of mysqldump... –  Katherine Villyard Jan 5 '14 at 19:26
    
I think your interpretation might be correct. I read "dump" as "backup" because in MySQL they are basically the same thing. –  Iain Elder Jan 6 '14 at 1:59
up vote 1 down vote

SQL Server has no exact equivalent of the mysqldump utility.

As Katherine said, SQL Server's default backup format is a binary file. It serves the same basic purpose as a dump file - you use it to restore a database. But you can't read it in a text editor - you need special tools to interpret it.

If you just want a backup of the whole database or a single table, there are a couple of utilities you can invoke using PHP's exec function.

To backup a single table, use the bcp utlity.

bcp dbo.$dbtable out $name.bcp -U $dbuser -P $dbpass -S $dbhost -d $dbname

To backup the whole database, use the sqlcmd utility and the BACKUP DATABASE command.

sqlcmd -S $dbhost -d $dbname -U $dbuser -P $dbpass -Q "BACKUP DATABASE $dbname TO FILE '$name.bak';"

If you need something more like mysqldump, you could use a PowerShell script or a third-party tool.

With PowerShell SMO (Server Management Objects), you can use the Scripter class and the ScriptingOptions class to script any object.

While researching the answer I found a third-party tool called sqlserverdump, "a command line utility inspired by mysqldump". I've not tried it yet, but it might be just what you need.

share
    
thanks..let me try it out.. –  faroke moahmed Jan 6 '14 at 6:59

Not the answer you're looking for? Browse other questions tagged or ask your own question.