Backup Extended Stored Proc doesn't work

SamCSamC Posts: 28
edited April 29, 2005 7:02AM in SQL Backup Previous Versions
My 15 minute transaction log backups stopped working Friday at 2PM EST.

If I manually execute the stored proc command in QA, I get no error message and neither do I get a backup of the transaction log.

Running the GUI, all works fine.

Also - The FULL database backup stopped working in the extended stored proc. Again, the GUI works fine.

I tried to uninstall the Extended Stored proc and reinstalled. No change. The extended procs don't work, the GUI does.

Sam
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    In QA, what is the output when you run the following:

    master..sqlbackup

    master..sqlbackup '-sql'

    Also, does the command line interface work?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SamCSamC Posts: 28
    petey wrote:
    In QA, what is the output when you run the following:

    master..sqlbackup
    Msg 20001, Level 1, State 20001
    Error executing extended stored procedure: Invalid # of Parameters
    petey wrote:
    In QA, what is the output when you run the following:
    master..sqlbackup '-sql'

    Also, does the command line interface work?
    Output from sqlbackup {nothing}
    0 Rows affected

    I've never run the command line interface. Suggest a test and I'll run it.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Can you run the following from the cmd line and let us know the output:

    sqlbackupc -sql "BACKUP DATATABASE pubs TO DISK = 'C:\pubs.sqb'"

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SamCSamC Posts: 28
    As I said, the GUI works.

    You want to see if the command line works. Here's the result:

    D:\Program Files\Red Gate\SQL Backup>sqlbackupc -sql "BACKUP DATABASE pubs TO DI
    SK = 'D:\pubs.sqb'"

    SQL Backup 3.1.0, (c) Red Gate Software Ltd 2004 - 2005
    Serial number: 010-001-020675-E71E

    Backing up pubs (full database) to D:\pubs.sqb ...

    Backup data size : 1.938 MB
    Compressed data size: 162.000 KB
    Compression rate : 91.83%
    Process completed successfully.

    Processed 160 pages for database 'pubs', file 'pubs' on file 1.
    Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
    BACKUP DATABASE successfully processed 161 pages in 0.039 seconds (33.660 MB/sec
    ).
    (1 row affected)
    · Share on Twitter
  • SamCSamC Posts: 28
    I see there's a "LOG" command.

    Should we add it to the command, execute it as a stored proc in QA (it fails) and inspect the LOG output?
    · Share on Twitter
  • SamCSamC Posts: 28
    I've got some news...

    Launched QA, ran the stored proc. Nothing, as in my earlier post.

    Immediately after this failure, I added: , LOGTO = ''d:\sam.txt'' to the end of the list of the stored proc commands.

    Pressed F5. It ran correctly. The backup of the full database worked.

    Seems like there is some permission problem that developed (The GUI and the command line run with Windows Authentication), QA and scheduled jobs run under another User altogether.

    I found no documentation on where the "default" LOGTO location might be. And I'll continue looking.

    One more question: does <AUTO> work in LOGTO = ''d:\<AUTO>''

    What is the solution and cause of this problem? These scripts ran correctly for almost a week.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Default log file location is <Documents and Settings folder>\All Users\Application Data\Red Gate\SQL Backup\Log.

    <AUTO> does not work with the LOGTO option.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SamCSamC Posts: 28
    petey wrote:
    Default log file location is <Documents and Settings folder>\All Users\Application Data\Red Gate\SQL Backup\Log.

    <AUTO> does not work with the LOGTO option.
    Right you are. Interesting finding there. The last log written was Friday, 1:45PM EST. The time my last backup ran successfully.

    The log files appear again, with each test I ran using the GUI, then again when I added the LOGTO option.

    I'm left having no idea what is the problem, but I'm glad it's running with the scotch tape applied.

    I look forward to a more solid explanation and solution.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You mentioned that different users are used when using the GUI and QA. If you used the same user you used for the GUI in QA, does the backup work without the LOGTO option?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • SamCSamC Posts: 28
    I was using Windows Authentication in the GUI and an SQL account in QA.

    Oddly, the problem has passed and the backups have been running since Tuesday of this week without the LOGTO option.

    I'll keep an eye on things to see if this situation develops again.

    Thanks,

    Sam
    · Share on Twitter
Sign In or Register to comment.