Knowledge Base

Hint Ref: 020707040006
Hint Date: 04/07/2007

Hint Details:

EFFECTED VERSION: Sybase 8 
 

Problem:

Using a database event to backup the database to a local directory on the server works OK, however it does not work to a network location,

Solution:

This is because the Database service does not have rights to the network location (if using  the System Account) and it is more reliable to run tasks using procedures and functions than directly from the event.

1. Put the backup command into a Database Function:

CREATE function spaceman.dobackup()
returns char(1)
begin
 message('Doing Backup');
   if db_name='Spaceman' then begin
   backup database directory '\\\\192.168.101.8\\data\\dbbackup\\livebackup'
   TRANSACTION LOG TRUNCATE;
   end;
  end if;
   message('Backup Completed')
end

2. Within the Database Event, call the function:

CREATE EVENT SMDailyBackup
SCHEDULE daily_backup
START TIME '7:00AM' EVERY 24 HOURS
HANDLER
  call spaceman.dobackup()
  END

3. Within the Sybase Service configuration go to the 'Account' tab and select 'Other Account'. Select a user who has rights to the database location and the network location

4. Restart the service.

NOTE: The backup will only run if the database name, set up in the serve with '-n', is 'Spaceman'. If this is not the correct database name, change it in the function.  This is to ensure that a demo copy of the database does not cause the live backup to be overwritten by the event running from the demo database.

(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)