Comments
12 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 commandmaster..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 resultsOutput 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. -
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. -
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 ?????? -
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 Entries2005-11-18 08:06:35.70 spid59 Changing the status to PAUSE for full-text catalog "FogBUGZCatalog" (5) in database "FogBugz" (5). 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" (5) in database "FogBugz" (5). 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(time): 2005/03/07(13:12:10), pages dumped: 12376, first LSN: 40:3105:37, last LSN: 40:3121:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_{6663CCAF-3850-4C77-973D-565BAD86579A}'}). 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" (5) in database "FogBugz" (5). 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" (5) in database "FogBugz" (5). 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(time): 2005/03/07(13:12:10), pages dumped: 12376, first LSN: 40:2936:234, last LSN: 40:3029:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_{D8EBB9BC-89B0-44FE-AB4C-0B877153FC98}'}). 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(R) Server Maintenance Utility (Unicode) 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 -
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? -
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.
Add comment
Please sign in to leave a comment.
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 ?