SQL Server 2005 Return Value Failure

sforkersforker Posts: 8
edited November 18, 2005 11:57AM in SQL Backup Previous Versions
Ok, first off, if SQL-Server 2005 Standard edition is NOT support, then please correct your website, or make it consistent across all the pages.
Under your "Road-Map" Section, there is a statement

"We will be releasing version 4.x of SQL Backup in early 2006, which will include full SQL Server 2005 enablement . (Note that SQL Backup 3.x works perfectly with SQL Server 2005)."
Ok, so, "perfectly with SQL Server 2005", let's continue ;-)

When I run a backup job from a Maintenance Plan, it says the step fails, but gives no info.
so I then run the job directly from the SQLStudio in the new query window.
This is the result I get.....

Output from SQL Backup (3.2.0)
--------------------------------------------------------------------------------------------------------------------------------
SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005                                                                         
Serial number: xxxxxxxxxx ware Ltd 2004 - 2005                                                                         
Backing up FogBugz (full database) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 093525.sqb ...                        
Backup data size    : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 093525.sqb ...                        
Compressed data s   : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 093525.sqb ...                        
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.


Now, both from the query window, and the Maintenance plan, the backup file IS created, and looks ok.
it is 1K smaller then if I run the backup from SQLBackup.exe (consistently, always a 1K smaller not matter the order, etc....)


here is the Job Command....
master..sqlbackup '-SQL "BACKUP DATABASE [FogBugz]  TO DISK = ''D:\SQLBackups\FogBugZ\<AUTO>.sqb'' WITH NAME = ''Database (FogBugz)'', DESCRIPTION = ''Database: FogBugz  Instance: (local)  Server: DBSERVER3'', INIT, PASSWORD = ''<ENCRYPTEDPASSWORD>myawMmAjeTlw</ENCRYPTEDPASSWORD>'', ERASEFILES_ATSTART = 3, COMPRESSION = 2" -U sa -P <LOGINPASSWORD>m0c5ZcLWc23okCc3</LOGINPASSWORD>'

Is this just a return value problem ?

Comments

  • Hello,

    This could be a problem with the software itself, rather than its' interaction with SQL 2005. There are these longstanding issues with the OSQL.exe path, %ALLUSERSPROFILE% variable and access rights, etc.

    Can you please try applying the latest update to the software and see if that helps?

    It's at ftp://ftp.red-gate.com/sqb3.2.0.5.zip.
  • Ok,
    Updated software, fired up SQLBackup.exe, confirmed 3.2.0.5 from the About Box.
    Did a backup from within there, just to make sure everything is still cool there.

    Worked fine,

    Ok, then I attempted to run the Maintenance Plan from SQL Agent.
    That still failed with a generic "The Step Failed"

    I then open a query window and executed it from there.
    Results....
    Output from SQL Backup (3.2.0)
    --------------------------------------------------------------------------------------------------------------------------------
    SQL Backup 3.2.0.5, (c) Red Gate Software Ltd 2004 - 2005                                                                       
    Serial number: xxxxxx ftware Ltd 2004 - 2005                                                                       
    Backing up FogBugz (full database) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 110738.sqb ...                        
    Backup data size    : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 110738.sqb ...                        
    Compressed data     : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 110738.sqb ...                        
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    


    Now, all 3 steps above did create the .sqb files, and they are now ALL the same exact size down to the byte.
    Have NOT done a binary comparsion yet, have to upload a utility.

    I'm logged in as administrator (thru a remote desktop connection)
    It's a Windows Server 2003 Standard SP1 (Single Xeon, 3GB Memory - RAID5 )
    It only runs SQLServer, no other software besides OS components.
    (does have IIS installed because SQLServer 2005 wants it installed for some of its features)

    So, still the same problem
  • What steps did you go through to set up this maintenance plan? Have you set it up as a SQL Server Agent Task, or an execute T-SQL task?

    Are you attempting to do multiple backups to the same filename? You cannot do this without specifying the INIT command...
  • I'm going to detour and focus on it failing when running the query directly. I figure this has less variables then doing it thru the maintenance plan.

    I'm bringing up the MS SQL Server Management Studio
    I'm logging in as 'sa', and have the 'master' database selected. - selecting 'new query'
    The file is getting created in the directory, even when it fails, so I think write permission has been proven.
    In this latest test, I turned off overwriting, deleteing past backups, etc....
    I also cleared out the directory totally before running.
    I also do no encrytion in this one....

    here is the command
    master..sqlbackup '-SQL "BACKUP DATABASE [FogBugz]  TO DISK = ''D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 123334.sqb'' WITH NAME = ''Database (FogBugz), 11/16/2005 12:33:08 PM'', DESCRIPTION = ''Backup on 11/16/2005 12:33:08 PM  Database: FogBugz  Instance: (local)  Server: DBSERVER3'', COMPRESSION = 2" -U sa -P <LOGINPASSWORD>m0c5ZcLWc23okCc3</LOGINPASSWORD>'
    
    Here are the results
    Output from SQL Backup (3.2.0)
    --------------------------------------------------------------------------------------------------------------------------------
    SQL Backup 3.2.0.5, (c) Red Gate Software Ltd 2004 - 2005                                                                       
    Serial number: xxx ftware Ltd 2004 - 2005                                                                       
    Backing up FogBugz (full database) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 123334.sqb ...                        
    Backup data size    : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 123334.sqb ...                        
    Compressed data     : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051116 123334.sqb ...                        
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    


    It fails, but the file is created, it's the only one in the directory now, appears to be fine, I'm restoring now to make sure.
  • peteypetey Posts: 2,358 New member
    It looks like there is an error with the execution of SQL Backup, but the error failed to be reported.

    Could you pls try running the same command from the command line interface (SQLBackupC.exe)? Also, are there any other information logged in the log files (found in C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log)?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Sorry for the delay in repsonse, I was actually at the MS VS 2005/SQL2005 launch event in Philly yesterday.

    * I clear out both the backup directory and log directory

    I haven't got it to run yet from the command line, I cut and Paste from the SQLbackup Program / Command line format window in the last step of the backup wizard.
    It produces
    "C:\Program Files\Red Gate\SQL Backup\SqlBackupC.exe" -SQL "BACKUP DATABASE [FogBugz]  TO DISK = 'D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb' WITH NAME = 'Database (FogBugz), 11/18/2005 7:43:25 AM', DESCRIPTION = 'Backup on 11/18/2005 7:43:25 AM  Database: FogBugz  Instance: (local)  Server: DBSERVER3', COMPRESSION = 1" -U sa -P <LOGINPASSWORD>m0c5ZcLWc23okCc3</LOGINPASSWORD>
    

    I have tried a couple of things to fix it, but I keep getting "The Syntax of the command is incorrect"
    Here is the Log file that it produces...
    11/18/2005 7:46:58 AM: SQL Backup 3.2.0.5, (c) Red Gate Software Ltd 2004 - 2005 
    11/18/2005 7:46:58 AM: 
    11/18/2005 7:46:58 AM: Syntax error: ' after =
    


    * I again clear out both the backup directory and log directory to prevent overwrite errors

    When I run it directly from you SQLBackup program, it works as expected and generates the following log file...
    11/18/2005 7:51:44 AM: SQL Backup 3.2.0.5, Copyright (c) 2005 Red Gate Software
    11/18/2005 7:51:44 AM: 
    11/18/2005 7:51:44 AM: BACKUP DATABASE [FogBugz]  TO DISK = 'D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb' WITH NAME = 'Database (FogBugz), 11/18/2005 7:43:25 AM', DESCRIPTION = 'Backup on 11/18/2005 7:43:25 AM
    Database: FogBugz
    Instance: (local)
    Server: DBSERVER3', COMPRESSION = 1
    11/18/2005 7:51:44 AM: 
    11/18/2005 7:51:44 AM: osql -S DBSERVER3 -i "C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\sqb{53CFA62E-2488-496C-9033-6ACB47DE4D96}.txt" -b -w 120 -U sa -P XXXXXXXXXX
    11/18/2005 7:51:48 AM: Backup data size    : 96.688 MB
    11/18/2005 7:51:48 AM: Compressed data size: 51.995 MB
    11/18/2005 7:51:48 AM: Compression rate    : 46.22%
    11/18/2005 7:51:50 AM: Process completed successfully.
    11/18/2005 7:51:50 AM: 
    11/18/2005 7:51:50 AM: 11/18/2005 7:51:44 AM: Backup started ...
    11/18/2005 7:51:50 AM: Backup completed.
    11/18/2005 7:51:50 AM: Data size: 96.688 MB
    11/18/2005 7:51:50 AM: Compressed size: 51.995 MB
    11/18/2005 7:51:50 AM: Compression ratio: 46 %
    
    ----- SQL Server messages -----
    Processed 8064 pages for database 'FogBugz', file 'FogBugz_Data' on file 1.
    Processed 4079 pages for database 'FogBugz', file 'sysft_FogBUGZCatalog' on file 1.
    Processed 1 pages for database 'FogBugz', file 'FogBugz_Log' on file 1.
    BACKUP DATABASE successfully processed 12144 pages in 3.110 seconds (31.986 MB/sec).
    (1 row affected)
    

    * I again clear out both the backup directory and log directory to prevent overwrite errors

    Then I take the Extended SP command at the end of the wizard, and paste in into a query window and it fails...
    master..sqlbackup '-SQL "BACKUP DATABASE [FogBugz]  TO DISK = ''D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb'' WITH NAME = ''Database (FogBugz), 11/18/2005 7:43:25 AM'', DESCRIPTION = ''Backup on 11/18/2005 7:43:25 AM  Database: FogBugz  Instance: (local)  Server: DBSERVER3'', COMPRESSION = 1" -U sa -P <LOGINPASSWORD>m0c5ZcLWc23okCc3</LOGINPASSWORD>'
    

    Query Results...
    Output from SQL Backup (3.2.0)
    --------------------------------------------------------------------------------------------------------------------------------
    SQL Backup 3.2.0.5, (c) Red Gate Software Ltd 2004 - 2005                                                                       
    Serial number: xxx ftware Ltd 2004 - 2005                                                                       
    Backing up FogBugz (full database) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb ...                        
    Backup data size    : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb ...                        
    Compressed data     : 96.688 MB e) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb ...                        
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command.  The results, if any, should be discarded.
    

    No New Log files in the SQLBackup/Log Directory
    The Backup file is generated and it appears to be a good backup

    I'm attempting now to launch the query in a OLD 2000 Query Analyzer program against the 2005 database
  • I logged into a SQLServer 2000 machine, and used the old Query Analyzer to run the same Command that fails when running from the new SQL mgmt studio.
    So, since the database is the same, 2005 standard, the only thing this test changes is the program, Query Analyzer, and how the old program actually makes a connection to the database.

    Ok, strange, the results look the same (they seem to get cut off), but NO ERROR code is returned...
    Output from SQL Backup (3.2.0)                                                                                                   
    -------------------------------------------------------------------------------------------------------------------------------- 
    SQL Backup 3.2.0.5, (c) Red Gate Software Ltd 2004 - 2005 
    Serial number: xxxxx
    Backing up FogBugz (full database) to D:\SQLBackups\FogBugZ\FULL_(local)_FogBugz_20051118 074333.sqb ...
    Backup data size    : 96.688 MB
    Compressed data
    


    no log file is created, and the backup file IS Created.
    Not sure if it's failing still, but the old query analyzer is not seeing it or not caring about the return code or ??????
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Does running the command in Query Analyzer result in a log file being created in SQLBackup\Logs? Also please check <SQL Server's log file location>\VDI.Log. There may be some information in there.

    It looks to me like it's failing and trying to re-run part of the backup...
  • Running from the old Query Analyzer DOES NOT produce a red-gate log file.
    It does produce the actual backup file which looks complete.

    Ok. the only log entries I see from the test from the old Query Analyzer

    SQLServer Log Entries
    2005-11-18 08:06:35.70 spid59      Changing the status to PAUSE for full-text catalog "FogBUGZCatalog" &#40;5&#41; in database "FogBugz" &#40;5&#41;. This is an informational message only. No user action is required.
    2005-11-18 08:06:38.82 spid59      Changing the status to RESUME for full-text catalog "FogBUGZCatalog" &#40;5&#41; in database "FogBugz" &#40;5&#41;. This is an informational message only. No user action is required.
    2005-11-18 08:06:38.92 Backup      Database backed up. Database: FogBugz, creation date&#40;time&#41;: 2005/03/07&#40;13:12:10&#41;, pages dumped: 12376, first LSN: 40:3105:37, last LSN: 40:3121:1, number of dump devices: 1, device information: &#40;FILE=1, TYPE=VIRTUAL_DEVICE: &#123;'SQLBACKUP_&#123;6663CCAF-3850-4C77-973D-565BAD86579A&#125;'&#125;&#41;. This is an informational message only. No user action is required.
    


    BUT, I also noticed that the maintenance job I have running at 4:00am using red-gate (I kept it active even through it says it fails, but it does appear to do a backup)
    Produces these log entries...
    2005-11-18 04:00:01.20 spid55      Changing the status to PAUSE for full-text catalog "FogBUGZCatalog" &#40;5&#41; in database "FogBugz" &#40;5&#41;. This is an informational message only. No user action is required.
    2005-11-18 04:00:05.81 spid55      Changing the status to RESUME for full-text catalog "FogBUGZCatalog" &#40;5&#41; in database "FogBugz" &#40;5&#41;. This is an informational message only. No user action is required.
    2005-11-18 04:00:05.90 Backup      Database backed up. Database: FogBugz, creation date&#40;time&#41;: 2005/03/07&#40;13:12:10&#41;, pages dumped: 12376, first LSN: 40:2936:234, last LSN: 40:3029:1, number of dump devices: 1, device information: &#40;FILE=1, TYPE=VIRTUAL_DEVICE: &#123;'SQLBACKUP_&#123;D8EBB9BC-89B0-44FE-AB4C-0B877153FC98&#125;'&#125;&#41;. This is an informational message only. No user action is required.
    

    I see no errors, but if I look at the job history, it says it failed, "step failed".

    It almost like it purely about the actual return code being returned, I thin kt he actual backup is running without problem because the backup files are being created, they seem to be valid backups. The logs show no errors or normal operation.
  • I also found a red-gate log file in the sqlserver log directory from the maintenance plan running at 4:00am
    Microsoft&#40;R&#41; Server Maintenance Utility &#40;Unicode&#41; Version 9.0.1399
    Report was generated on "DBSERVER3".
    Maintenance Plan: Red-Gate Full Daily Backup
    Duration: 00:00:07
    Status: Succeeded.
    Details:
    

    Everything seems to think that is completes ok, but the return value is not being loved by sqlagent, or sql mgmt studio
  • peteypetey Posts: 2,358 New member
    First off, there is a problem with the extended stored procedure that causes the gibberrish to appear when running SQL Backup from SQL Management Studio. You can contact support for an updated extended stored procedure.

    Secondly, something is causing a termination of the output from SQL backup i.e. it should report the compressed size, the compression ratio, and the pages backed up. In your case, the output is incomplete.

    Do you get the same results when backing up another database?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • BTW -- the support email address is support@red-gate.com.
    We do have something newer than 3.2.0.5 that we can send you.
Sign In or Register to comment.