Will SQL Backup Pro restore Log Shipping to SQL Backup Lite?

abroadway

Hi,

We're thinking of up-grading to SQL Backup Pro to take advantage of Log Shipping.

Scenario:
1 Production DB server, currently using Lite, with Full and Diff Backups.
1 Hot Spare DB server, using Lite, which is Restoring production Diffs.

Do we only need SQL Backup Pro upgraded on the production machine that we wish to backup Logs FROM?
And then keep SQL Backup Lite on the Hot Spare DB Server?

Will this work?

Kind regards,

Adam

HJoyce

Yes this will work. However you won't be able to use the Log shipping wizard to set this up as that requires a pro license. You can only select servers which are activated with SQL Backup Pro in order to use the log shipping wizard, so having SQL Backup Pro licenses on both servers makes it much easier to set up.

A SQL Backup Lite license won't let you create a backup using encryption, and only allows compression level 1, however you can restore a backup which was encrypted or used a different compression level, that's no problem.

Steps:

1. Make a full backup of the database on the production server
2. Restore it on the spare server, and make sure the recovery completion state is either NORECOVERY or STANDBY
3. Create a job on the production server to backup the log and copy it to a network share location. Schedule it to run on a recurring basis.

e.g.
--Transaction log script
EXECUTE master..sqlbackup N'-SQL "BACKUP LOG [DBName] TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\<TYPE>_<DATABASE>_<DATETIME YYYYmmddhhnnss>.sqb'' WITH COMPRESSION = 1, COPYTO = ''\\share\logshippingDirectory''"'


4. Create a job on the spare server to restore the log, and move the restored backup logs from the network share location after it's successfully restored. Schedule it to run on a recurring basis, preferably with an offset of a few minutes from the backup job.

DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
EXECUTE master..sqlbackup N'-SQL "RESTORE LOG [DBName] FROM DISK = \\share\logshippingDirectory\LOG_DBName_*.sqb'' WITH NORECOVERY, MOVETO = ''C:\CompletedLogBackups\BACKUP''"', @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)
END


Some of the advantages of using the Log shipping wizard are that it will create the jobs for you, make sure the database you are restoring the backups to is in the correct recovery completion state and check the permissions on the network share location.

abroadway

Thanks for the comprehensive reply.

(I was secretly thinking that grabbing the scripts created by Pro would achieve what you have succinctly laid out above.)

Could another option be to use the Wizard on the production server DB, to restore back onto itself (different DB name of course) and then cut and past the SQL Agent Job Steps onto the stand by server running Lite (adjusting the server names and paths accordingly)?

BTW: Ideally we'd love to run PRO on every server, but we're thinking of using SQL Backup across a farm of redundant servers and so its a cost saving exercise to use Lite where we can.

Adam

HJoyce

Yes you could do that, good idea.

The 14 day free trial of SQL Backup offers all the functionality available in a Pro license so as long as it's not expired yet on your spare server you can use the Log shipping wizard to set up the log shipping jobs.

By the way, if you contact Sales@red-gate.com directly you could let them know how many servers you have in order to get a discount for multiple SQL Backup Pro licenses.

abroadway

On a parting note, just want to say how brilliant your software is!

"Your software is brilliant!"

One thing that I'd love to see is a "Restore Only" version of SQL Backup.

Really handy for distributing around to all our SQL developers, so they can restore master copies of DB's backed up by PRO or LITE.

SQL Restore Only, cheap licence fee, just a thought!

Keep up the great work.

HJoyce

Glad you are getting on with SQL Backup, thanks for the positive feedback.

About your request for a restore only version... we don't offer that, however there is a utility sqb2mtf.exe which is shipped with the product (in the installation folder usually C:\Program Files\Red Gate\SQL Backup) which you can distribute freely which converts a SQL Backup backup file into native SQL Server format (which is un-compressed and un-encrypted).

This means if you want to give your SQL Developers a backup file compressed using SQL Backup you can also give them this utility and they can convert it and restore it using SQL Server without having to have a SQL Backup license.

There's information in the help file. The online version of the help file has a topic here: http://help.red-gate.com/help/SQLBackup ... ml#sqb2mtf).
You can also download a user interface to this utility to make life easier
http://www.red-gate.com/messageboard/vi ... php?t=4861