Backup Extended Stored Proc doesn't work

• 
  In QA, what is the output when you run the following:
  
  master..sqlbackup
  
  master..sqlbackup '-sql'
  
  Also, does the command line interface work?

  petey April 24, 2005 07:44

  - 0 +
• 

  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.

  SamC April 24, 2005 09:00

  - 0 +
• 
  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.

  petey April 24, 2005 13:28

  - 0 +
• 
  As I said, the GUI works.
  
  You want to see if the command line works. Here's the result:
  
  \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 \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)

  SamC April 24, 2005 13:58

  - 0 +
• 
  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?

  SamC April 24, 2005 14:02

  - 0 +
• 
  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.

  SamC April 24, 2005 14:14

  - 0 +
• 
  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.

  petey April 24, 2005 19:37

  - 0 +
• 

  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.

  SamC April 24, 2005 23:01

  - 0 +
• 
  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.

  petey April 29, 2005 00:07

  - 0 +
• 
  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

  SamC April 29, 2005 07:02

  - 0 +

Add comment

Please sign in to leave a comment.