How to schedule a full daily restore?

kronosmen

OK, I am officially losing my mind with this program.

I have been working with it since 2 PM today, it is now 10 PM.

I can NOT figure out how to schedule a daily Full database restore in this application?

The full backup is no problem to create and schedule.
The log shipping works great.

How in the heck does one schedule a daily full restore in you application?

All I want to do is a full backup of one database on the source server at 6 AM.
A restore on the target server of that same database a few minutes later using the source server backup file that was created above.

Then Log ship every twenty minutes between 7 AM and 4 AM.

Thanks for your help in advance,
marty

petey

The following comments apply to the setting up of the full backup and restore tasks.

You need to set up a SQL Server Agent job to perform the periodic full restore. The job step would be a call to the SQL Backup extended stored procedure to perform the restore task e.g.

DECLARE @exitcode int 
DECLARE @sqlerrorcode int 

EXEC master..sqlbackup '-sql "RESTORE DATABASE ... FROM DISK = ..." ', @exitcode OUTPUT, @sqlerrorcode OUTPUT 

IF (@exitcode <> 0) OR (@sqlerrorcode <> 0) 
BEGIN 
  RAISERROR ('SQL Backup step failed: exit code %d, SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) 
END

2 elements in the restore process would need further thought:

1. Backup file location
You may need to access a shared folder on the source server to use the backup file. You may also use a COPYTO command in the full backup task to copy the backup file to the standby server. The COPYTO option would need to be added manually, as it is not an option in the GUI e.g.

exec master..sqlbackup '-sql "BACKUP ... WITH COPYTO = [\\standby\backups] " '

When accessing network shares, you need to ensure that the SQL Backup service startup user has access rights.

2. Backup file name
If you use the same name for the backup file consistently, then this is not an issue. However, if you do use dynaminc names e.g. timestamped, you would need use the MOVETO option in the restore process, to move the restored file to another path so that SQL Backup does not attempt to restore it repeatedly e.g.

- backup tasks completes, generating a backup file named 'FULL_mydb_20070108 0130.sqb' in a folder named 'g:\backups\' on the standby server

- for the restore task, you can use the wildcard naming convention to pick up the file e.g.

exec master..sqlbackup '-sql "RESTORE DATABASE mydb FROM DISK = b]g:\backups\FULL_mydb_2007*[/b WITH MOVETO = [g:\backups\processed]" '

This will cause SQL Backup to perform the restore using all files matching the 'FULL_mydb_2007*' pattern in the 'g:\backups' folder. The MOVETO option then moves this file off to another folder, so that it does not get picked up the next time the restore task runs.

kronosmen

Peter,

I will give it a try.

In the meantime, I have gone back to my home-grown backup SQL agent backup and restore jobs for both servers. Working fine.

I was trying your product in the hope that the GUI would allow me to train the techs to maintain their own departments' disaster recovery processes.

I have to honestly say that I am disappointed that your GUI only goes 3/4 of the way to a complete disaster recovery solution.

Your product's inabiltiy to do scheduled peroidic full backups and restores to a remote backup server from within your product's GUI would seem to prevent it from being complete.

For a new DBA it would then be a perfect solution. IMHO

thanks,
marty