none
Backup database

    Question

  • When taking backup with php I received an 3041 error and I have tried change timeout option in sqlsrv_query too.

    sqlsrv_configure("WarningReturnAsErrors",0 );
        sqlsrv_configure("LogSeverity", 0); 
        $sqlstmt = "BACKUP DATABASE  dbname TO  DISK = N'C:\\dbname.bak' WITH NOFORMAT, NOINIT,  NAME = N'Full Database Backup', SKIP, NOREWIND, NOUNLOAD";
        $stmt = sqlsrv_query($BdSrcConn, $sqlstmt);
       
        echo "<br/>SQLSTMT = $sqlstmt <br/>" ;
        var_dump($stmt);
        if ($stmt){ echo "<br/>backup completed: $stmt";}
        else var_dump(sqlsrv_errors());

    I try too usleep(100) and the backup is not completed .

    The error messages is

              296 pages trait�es pour la base de donn�es 'dbname', fichier 'dbname' dans le fichier 1.

    In SQL server error log I have 3041 error message without any others messages in Windows application log.

    Any ideas.

     

     

    Monday, February 21, 2011 4:27 PM

Answers

  • Did you post your whole php script?  Here's a browser script that works for me.  Notice that I call sqlsrv_next_result ().  The output of my script is at the end.

    header('content-type: text/plain;charset=UTF-8');
    
    $query = "
    BACKUP DATABASE dbname TO DISK = N'C:\\dbname.bak' 
    WITH NOFORMAT, NOINIT, NAME = N'dbname Database Backup Test', 
    SKIP, NOREWIND, NOUNLOAD
    ";
    
    $conn = sqlsrv_connect('SERVER',array('UID'=>'USER', 'PWD'=>'PASSWORD','Database'=>'master','CharacterSet'=>'UTF-8'));
    
    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);
    

    Output:

    Array
    (
     [0] => Array
      (
       [0] => 01000
       [SQLSTATE] => 01000
       [1] => 4035
       [code] => 4035
       [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 77728 pages for database 'dbname', file 'dbname' on file 3.
       [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 77728 pages for database 'dbname', file 'dbname' on file 3.
      )
    
    )
     * ---End of result --- *
    Array
    (
     [0] => Array
      (
       [0] => 01000
       [SQLSTATE] => 01000
       [1] => 4035
       [code] => 4035
       [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 1 pages for database 'dbname', file 'dbname_log' on file 3.
       [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 1 pages for database 'dbname', file 'dbname_log' on file 3.
      )
    
    )
     * ---End of result --- *
    Array
    (
     [0] => Array
      (
       [0] => 01000
       [SQLSTATE] => 01000
       [1] => 3014
       [code] => 3014
       [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP DATABASE successfully processed 77729 pages in 10.585 seconds (60.156 MB/sec).
       [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP DATABASE successfully processed 77729 pages in 10.585 seconds (60.156 MB/sec).
      )
    
    )
     * ---End of result --- *
    

    Rob
    Tuesday, February 22, 2011 2:33 PM

All replies

  • I think I ran into the same problem (don't remember for sure) when writing this article about backup and resotre: http://blogs.msdn.com/b/brian_swan/archive/2010/04/06/backup-and-restore-with-the-sql-server-driver-for-php.aspx

    When you execute your BACKUP query, what database are you connected to? I think you need to connect to master when backing up another database.

    Let us know if that doesn't help...I'll look again.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Monday, February 21, 2011 5:36 PM
  • I'm connected on Master database by default.

    I had already read your blog but without success . The backup start but not terminate, I receive the first line from the backup feedback.

    I execute with same user the backup command on management studio and work well. I have the 2.0.1 sql server driver version for PHP.

    Thank's


    Eric

    Monday, February 21, 2011 6:37 PM
  • The next thing I'd check is permissions - it's possible that the credentials under which your web server is running don't have access to C drive. One way to test this is to run your script from the command line (which will run PHP under your credentials). Another way is to change the destination of your backup file to a location (such as C:\Users\Public\Desktop) that anyone can write to. Or you can figure out the credentials under which php-cgi.exe is running and add write permissions to C drive (this should help with that: http://blogs.msdn.com/b/brian_swan/archive/2010/02/10/sql-server-driver-for-php-understanding-windows-authentication.aspx).

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    • Marked as answer by dauphin2008 Tuesday, February 22, 2011 1:42 PM
    • Unmarked as answer by dauphin2008 Tuesday, February 22, 2011 1:43 PM
    Tuesday, February 22, 2011 3:15 AM
  • It's work fine now. I have put an usleep(300) after sqlsrv_query. Before I had tried with usleep(100) but without success.

    If the time of backup take longer, I should increase the value of usleep.

    Thank you for your help.

    Eric

    Tuesday, February 22, 2011 1:49 PM
  • Did you post your whole php script?  Here's a browser script that works for me.  Notice that I call sqlsrv_next_result ().  The output of my script is at the end.

    header('content-type: text/plain;charset=UTF-8');
    
    $query = "
    BACKUP DATABASE dbname TO DISK = N'C:\\dbname.bak' 
    WITH NOFORMAT, NOINIT, NAME = N'dbname Database Backup Test', 
    SKIP, NOREWIND, NOUNLOAD
    ";
    
    $conn = sqlsrv_connect('SERVER',array('UID'=>'USER', 'PWD'=>'PASSWORD','Database'=>'master','CharacterSet'=>'UTF-8'));
    
    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);
    

    Output:

    Array
    (
     [0] => Array
      (
       [0] => 01000
       [SQLSTATE] => 01000
       [1] => 4035
       [code] => 4035
       [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 77728 pages for database 'dbname', file 'dbname' on file 3.
       [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 77728 pages for database 'dbname', file 'dbname' on file 3.
      )
    
    )
     * ---End of result --- *
    Array
    (
     [0] => Array
      (
       [0] => 01000
       [SQLSTATE] => 01000
       [1] => 4035
       [code] => 4035
       [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 1 pages for database 'dbname', file 'dbname_log' on file 3.
       [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Processed 1 pages for database 'dbname', file 'dbname_log' on file 3.
      )
    
    )
     * ---End of result --- *
    Array
    (
     [0] => Array
      (
       [0] => 01000
       [SQLSTATE] => 01000
       [1] => 3014
       [code] => 3014
       [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP DATABASE successfully processed 77729 pages in 10.585 seconds (60.156 MB/sec).
       [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]BACKUP DATABASE successfully processed 77729 pages in 10.585 seconds (60.156 MB/sec).
      )
    
    )
     * ---End of result --- *
    

    Rob
    Tuesday, February 22, 2011 2:33 PM
  • Here's my code that work.  It's not return all details of backup process. I placed the default database for sql user login in master database; I have increase the usleep value to 600 for all of my needs.

    sqlsrv_configure("WarningsReturnAsErrors",0);
    $sqlstmt = "BACKUP DATABASE dbname TO DISK = N'C:\\dbname.bak' WITH NOFORMAT, INIT";
    
    $DbSrcConn=OpenSrcDbConnection();
    $stmt = sqlsrv_query($DbSrcConn, $sqlstmt);
    usleep(600);
    
    if ($stmt){ 
    	echo "<br/>\t backup completed <br/>";
    	$DONE = TRUE;
    }
    else {
    	echo "ERROR ON BACKUP <br/> Message : ";
    	var_dump(sqlsrv_errors());
    }
    unset ($stmt,$sqlstmt);
    sqlsrv_close ($DbSrcConn);

    - Eric

     

    Tuesday, February 22, 2011 3:30 PM
  • You're welcome Eric.

    When 600 ms stops working, you can try my solution; but in a way, I wish I hadn't posted it.

    By the way, do you know how long 600 milliseconds is?

    I don't want to know, this is a rhetorical question.  It is 0.6 seconds.  What's the point in that? It's only working by fluke.

    When it goes wrong again, maybe you should un-mark your answer.

    Edit: actually it's 600 micro seconds, which is 0.0006 seconds!  Even more pointless.


    Rob
    • Edited by Robert Johnson Wednesday, February 23, 2011 11:07 AM microseconds
    Wednesday, February 23, 2011 11:02 AM
  • I know that 600 ms is not long but is seem that is enough for the driver to run the backup.
    Anyway , it's not normal that we should take a work around to execute a backup command with a driver.

    I take note your code in case the 600 ms stop working.

    thank's

    Eric.

     

    Wednesday, February 23, 2011 12:53 PM
  • Robert Johnson

    Thank you Thank you Thank you!!!!!

    As a lol - my system worked beautifully, right up until I had to demo for the customer and this error popped up - always the case isn't it.... Your fix worked wonderfully, the usleep thing had no effect - I am very very glad you made this post!!!

    Thanks you!


    • Edited by swninetails Wednesday, January 04, 2012 5:25 PM
    Wednesday, January 04, 2012 5:25 PM
  • Robert Johnson's solution solved my issue as well. 

    My SQL 2008 server was returning a 3013 error code to PHP 5.3.8. 

    The SQL Server logs had a 3041 error, with "BACKUP failed to complete the command BACKUP DATABASE msdb. Check the backup application log for detailed messages."

    Neither of these messages are explained anywhere that I could find with several days of desperate searching on the web.

    Database and differential backups were failing, but TLOG backups succeded.

    Using ProcessMonitor, I was able to see sqlservr.exe write the files and then delete them.

    Thanks for this solution!

    Otto Monnig

    Zend Certified Engineer


    • Edited by Otto Monnig Monday, January 16, 2012 4:37 PM
    Tuesday, January 10, 2012 6:31 PM
  • Actually, Robert's answer is the correct one - I mean to write a blog post about this. There are two ways to make sure this works as you expect:

    1. Set "SET NOCOUNT ON" in the query. This will ensure that the resultset is not returned for every statement which gets executed on the server. This will avoid "sqlsrv_query" returning immediately after the first resultset.

    2. Consume the results using "sqlsrv_next_result" until all operations are executed on the server.

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.
    Tuesday, January 10, 2012 7:38 PM
  • Additional notes:

    You can use the method in Robert Johnson's code example with RESTORE WITH VERIFY, but the return codes are different, see below:

    Successful BACKUP returns SQLSTATE 01000, code 3014

    Successful RESTORE WITH VERIFY returns SQLSTATE 01000, code 3262

     

    Time Delay

    Using a time delay in your code is a fragile, temporary, fix that will break when you least expect it.  Try instead to better understand the system/service that you are programming against.

    Otto

    Monday, January 16, 2012 4:53 PM