SQL Server 2005 Return Value Failure
sforker
Posts: 8
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.....
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....
Is this just a return value problem ?
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
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.
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....
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
Are you attempting to do multiple backups to the same filename? You cannot do this without specifying the INIT command...
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
Here are the results
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.
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
* 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
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...
* 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...
* 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...
Query Results...
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
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...
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 ??????
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...
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
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...
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.
Everything seems to think that is completes ok, but the return value is not being loved by sqlagent, or sql mgmt studio
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?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
We do have something newer than 3.2.0.5 that we can send you.