Backup process does not end

• 
  Hi
  
  The command you have given is slightly incorrect. Here is an amended version.
  master..sqlbackup '-SQL "BACKUP DATABASE [ISI] TO DISK = [d:\sql_data\MSSQL\BACKUP\<AUTO>] WITH LOGTO = [d:\sql_data\MSSQL\BACKUP\Logs\]" -E'
  The with logto, and other options, must be within the quotes (")
  
  You are also missing the credentials to login. Please use the -E option (as above) or -P and -U.
  
  Regards
  Dan

  Daniel Handley June 10, 2005 09:01

  - 0 +
• 
  Thanks, it was hanging before I added the LOGTO and is still hanging with the new version. Does it not always try to write to C:\Program Files\Red Gate\SQL Backup\ even when using LOGTO? And the docs say that -E is the default if it is not specified.

  Ulfius June 10, 2005 12:31

  - 0 +
• 
  Yes it will always try to write a log file here.
  This directory should be accessible to all users, can you check the permissions as it sounds like there is an access issue.
  
  Regards
  Dan

  Daniel Handley June 10, 2005 12:33

  - 0 +
• 
  I've cleared up the log file error, but still cannot get the process to end. Backing up this database from the GUI takes about two seconds, but issuing this command from QA or a scheduled job will cause it to sit for hours - until I cancel it. The .sqb file DOES get created.
  
  The log file is empty, no matter if I run from GUI or command line.
  
  6/13/2005 7:48:20 AM: SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
  6/13/2005 7:48:20 AM:

  Ulfius June 13, 2005 12:58

  - 0 +
• 
  Just to confirm something in your last post: when you backup the database from the GUI, the backup completes but the log file is incomplete? Or is it incomplete only when you run it from the cmd line and QA?
  
  If you run the same command from the cmd line but without the LOGTO option, do you encounter the same error?

  petey June 13, 2005 22:09

  - 0 +
• 
  This is the entire contents of the log file - after I ran both the GUI and the command line.
  

  ---

  6/13/2005 7:48:20 AM: SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
  6/13/2005 7:48:20 AM:

  ---

  
  I don't receive an error any longer, just the process never ends when ran command line, but finishes in two seconds using the GUI.

  Ulfius June 14, 2005 12:01

  - 0 +
• 
  Can you run the following from the command line on the backup file that was created from the cmd line (not the GUI), to check if the backup file is 'complete'?
  
  sqlbackupc -sql "RESTORE SQBHEADERONLY FROM DISK = '<your backup file>'"
  
  Do the LSN numbers have any values? Thanks.

  petey June 14, 2005 22:02

  - 0 +
• 
  I got this from running this command from QA:
  
  master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = [d:\sql_data\MSSQL\BACKUP\FULL_(local)_ISI_20050613 093646.sqb]" '
  
  ==============
  SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
  Trial - 2 days remaining
  Reading SQB file header of "d:\sql_data\MSSQL\BACKUP\FULL_(local)_ISI_20050613 093646.sqb"
  Backup group ID : 149794717
  File number : 1 of 1
  Backup type : 1 (Database)
  Backup size : 1.875 MB
  Backup start : Monday, June 13, 2005 9:36:52 AM
  Backup end : Monday, June 13, 2005 9:36:53 AM
  Server name : MARLIN
  Instance : (local)
  Database name : ISI
  First LSN : 18000000077600001
  Last LSN : 18000000077800001
  Checkpoint LSN : 18000000077600002
  Differential base LSN : 18000000077100003
  ================
  
  Then I ran this from QA and it would never finish.
  
  master..sqlbackup '-SQL "BACKUP DATABASE [ISI] TO DISK = [d:\sql_data\MSSQL\BACKUP\<AUTO>]" -E'

  Ulfius June 16, 2005 11:42

  - 0 +
• 
  The backup file appears to have been correctly generated. When running the backup from the command line, you mentioned that it never ended either. Could you post the output you receive when running from the command line? I would like to see at which stage it appears to be 'hanging'. Thanks.

  petey June 16, 2005 12:16

  - 0 +
• 
  I was incorrect when I said the command line also hung - I meant the stored procedure hung. I just tried the command line and it ran fine, but the stored procedure still does not stop in QA, and as a job it continues to say Executing Job Step '1 (Backup)'.
  
  C:\Program Files\Red Gate\SQL Backup>sqlbackupc -SQL "BACKUP DATABASE [ISI] TO D
  ISK = 'D:\SQL_DATA\MSSQL\BACKUP\<AUTO>'" -E
  
  SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
  Serial number: <removed>
  
  Backing up ISI (full database) to \SQL_DATA\MSSQL\BACKUP\FULL_(local)_ISI_2005
  0628 105414.sqb ...
  
  Backup data size : 1.875 MB
  Compressed data size: 198.500 KB
  Compression rate : 89.66%
  Process completed successfully.
  
  Processed 152 pages for database 'ISI', file 'ISI_dat' on file 1.
  Processed 1 pages for database 'ISI', file 'ISI_log' on file 1.
  BACKUP DATABASE successfully processed 153 pages in 0.129 seconds (9.660 MB/sec)
  .
  (1 row affected)

  Ulfius June 28, 2005 14:09

  - 0 +
• 
  Internally, the extended stored procedure calls the command line to perform it's work. Thus, it's strange that it works from the command line but not from the extended sp.
  
  When using the command line and the process completes, are you returned to the prompt immediately, or does it pause, waiting for you to hit a key before it returns to the prompt?

  petey June 28, 2005 21:52

  - 0 +
• 
  It immediately returns to the prompt. It also does not create a log file in c:\program files\red gate\sql backup\ but the SQL Server functional account has full access to that folder.

  Ulfius June 29, 2005 11:03

  - 0 +
• 
  I just noticed that, although there isn't a log file created in c:\program files\red gate\sql backup\ , when I start the GUI the Activity Log shows the backups I did via command line, but not the ones via QA.

  Ulfius June 29, 2005 11:10

  - 0 +
• 
  The backup issued via QA does show in the Backup History tab of the GUI, although the process does not finish.

  Ulfius June 29, 2005 11:14

  - 0 +
• 
  The log files are usually found in C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log, assuming C:\ is your system drive.
  
  Can you try running a simple backup process from Query Analyzer using the NOWRITE option e.g.
  
  sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [c:\pubs.sqb] WITH INIT, NOWRITE"'
  
  This will cause SQL Backup to skip updating the local activity history table, assuming that is the problem.

  petey June 29, 2005 11:45

  - 0 +
• 
  That's it - well almost. I see the NOWRITE option causes the backup file to not be created.
  
  This hung:
  
  master..sqlbackup '-sql "BACKUP DATABASE isi TO DISK = [D:\SQL_DATA\MSSQL\BACKUP\isi.sqb] WITH INIT"'
  
  This did not:
  
  master..sqlbackup '-sql "BACKUP DATABASE isi TO DISK = [D:\SQL_DATA\MSSQL\BACKUP\isi.sqb] WITH INIT, NOWRITE"'
  
  =======
  SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
  Serial number: <removed>
  Backing up isi (full database) to \SQL_DATA\MSSQL\BACKUP\isi.sqb ...
  Processed 152 pages for database 'isi', file 'ISI_dat' on file 1.
  Processed 1 pages for database 'isi', file 'ISI_log' on file 1.
  BACKUP DATABASE successfully processed 153 pages in 0.144 seconds (8.654 MB/sec).
  =========
  
  
  Is there a solution?

  Ulfius June 29, 2005 12:49

  - 0 +
• 
  The activity log is stored in the C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data folder. It should exist on your workstation.
  
  A possibility is that the SQL Server service startup account does not have 'write' rights to this folder.
  
  Try this in Query Analyzer:
  
  master..xp_cmdshell 'dir c: > "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\dir.txt"'

  petey June 29, 2005 13:11

  - 0 +
• 
  The dir.txt file does get created properly.

  Ulfius June 29, 2005 13:53

  - 0 +
• 
  The problem has been resolved, and was due to a permissions error on the SQL Server service startup account to write to the C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data folder.

  petey July 01, 2005 12:50

  - 0 +

Add comment

Please sign in to leave a comment.