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

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

abroadwayabroadway Posts: 12
edited January 9, 2008 10:18AM in SQL Backup Previous Versions

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

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 :)


  • Options
    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.


    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.

    --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)
    RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)

    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.
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • Options
    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 :)
  • Options
    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.
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • Options
    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.

  • Options
    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
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
Sign In or Register to comment.