Backup process does not end

Ulfius · June 9, 2005 2:37PM

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?

Daniel Handley · June 10, 2005 9:01AM

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

Ulfius · June 10, 2005 12:31PM

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.

Daniel Handley · June 10, 2005 12:33PM

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

Ulfius · June 13, 2005 12:58PM

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:

petey · June 13, 2005 10:09PM

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?

Ulfius · June 14, 2005 12:01PM

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.

petey · June 14, 2005 10:02PM

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.

Ulfius · June 16, 2005 11:42AM

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'

petey · June 16, 2005 12:16PM

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.

Ulfius · June 28, 2005 2:09PM

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)

petey · June 28, 2005 9:52PM

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?

Ulfius · June 29, 2005 11:03AM

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:10AM

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:14AM

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

petey · June 29, 2005 11:45AM

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.

Ulfius · June 29, 2005 12:49PM

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?

petey · June 29, 2005 1:11PM

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"'

Ulfius · June 29, 2005 1:53PM

The dir.txt file does get created properly.

petey · July 1, 2005 12:50PM

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.

Backup process does not end

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk