Backup process does not end

UlfiusUlfius Posts: 15
edited July 1, 2005 12:50PM in SQL Backup Previous Versions
I've setup a job to backup a database and the job process is never ending. I've also tried the same command in QA and it also never completes. Even canceling the job results in a waiting 10 minutes at "Attempting to cancel query. Please wait...".

The command is:

master..sqlbackup '-SQL "BACKUP DATABASE [ISI] TO DISK = [d:\sql_data\MSSQL\BACKUP\<AUTO>]" WITH LOGTO = [d:\sql_data\MSSQL\BACKUP\Logs\] '

Running master..sqlbackup '-SQL'
gives:

SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
Trial - 9 days remaining
login parameters:
[-U login id] [-P password] [-E] use trusted connection
[-I instance name]
common parameters:
[-SQL command]
[-USE setting description]
Warning 110: Failed to save primary log file: Cannot create file "C:\Program Files\Red Gate\SQL Backup\20050609 112747 01.log".


Would not having access to the log folder cause this?

Comments

  • 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
    Red Gate Software Ltd
  • 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.
  • 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
    Red Gate Software Ltd
  • 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:
  • peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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'
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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 D:\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)
  • peteypetey Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • UlfiusUlfius Posts: 15
    edited June 29, 2005 11:15AM
    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.
  • The backup issued via QA does show in the Backup History tab of the GUI, although the process does not finish.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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 D:\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?
  • peteypetey Posts: 2,358 New member
    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"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The dir.txt file does get created properly.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.