What are the challenges you face when working across database platforms? Take the survey
Options

log missing

camarasinghamcamarasingham Posts: 4
edited January 16, 2006 10:11PM in SQL Backup Previous Versions
Hi

I am having trouble trying to get the logs of successful backups. I know it is suppossed to be written to C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log but the logs there are only from those backup jobs which are failures. I can't find any logs for those backup jobs which have successfully completed as evidenced by the sql output.

Any help much appreciated.

I am using the Trial Version of SQLBackup 3.2.0.1 on SQL server Standard 2000 on a windows 2003 server.

Kind Regards
Chandra Amarasingham

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Chandra,

    SQL Backup logs should contain logging for all activities.

    Maybe, though, you'd want to consider using the MAILTO option to have the software email the logs to you automatically.
  • Options
    Thanks Brian,

    I tried the MAILTO option still no success in obtaining the log. However this failure only occurs when I used an SQLAgent job to run the SQL which runs the Redgate backup command using the sequence of sql shown below in a stored procedure. If I run redgate sqlbackup manually/interactively I am able to get the log both through email and through the normal text file stored on disk. Could it be due to user permssions? since sqlagent runs under a domain user account or is it something else?

    Thanks
    Chandra

    select @sql =

    '-SQL "BACKUP DATABASE
    TO DISK = ''' + @Path + '\' + @Name + '_' + convert(varchar(10),getdate(),112) + '_' + replace(convert(varchar(10),getdate(),108), ':', '') + '.sqb''
    WITH NAME = ''' + @Name + ' ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ''',
    DESCRIPTION = ''Backup on ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ' Database: ' + @Name + ''',
    LOGTO = '' + @Logpath + '',
    MAILTO = ''chandra@cbmi.org.au'',
    INIT,
    PASSWORD = ''<ENCRYPTEDPASSWORD>........</ENCRYPTEDPASSWORD>'',
    ERASEFILES = ' + convert(varchar(3),@RetentionPeriod) + ',
    COMPRESSION = 3" -E'

    exec master..sqlbackup @sql
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Probably. The SQL Agent account needs write access to %ALLUSERSPROFILE%\Application Data\Red Gate\SQL Backup\Logs.
  • Options
    Thanks,

    finally got it. I had to make sqlserver and sqlagent run with adminstrative credentials. Giving permissions to the log folder was not enough. Maybe someone at redgate would know exactly where and what permissions are needed when sqlbackup is run with non-administrative credentials which may be needed in some environments.

    Thanks again.
    Chandra
  • Options
    peteypetey Posts: 2,358 New member
    You shouldn't have to make such drastic changes (granting administrative credentials) to your setup.

    Could you pls try to run a SQL Server Agent (without administrative credentials) job containing a SQL Backup backup task, but with only basic options e.g.

    sqlbackup 'sql "BACKUP DATABASE pubs TO DISK = '<somewhere>'"

    and see if the log is created?
    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.