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

Log Shipping on a WAN

mdolanmdolan Posts: 11
edited February 22, 2007 11:56AM in SQL Backup Previous Versions
We are moving a 8GB database from one location to another. I want to use log shipping across a WAN to keep the DB's sync'd.

I can send a full backup of the DB to the destination via an external HD then send the log files via ftp.

Do I need to setup a log shipping job on source machine even though there isn't a local destination machine?

Do I need to create the the identical share names on the real destination server and copy the log files there?

Am I way off base here?
Matt Dolan
T-Systems International

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Let's look at the log shipping requirements:

    - create log backups on the source box
    - send the backup files to the standby box
    - restore the log backup on the standby box

    SQL Backup helps with the last 2 steps by adding some custom extensions.

    To get the backup file to the standby box, there is a COPYTO option, that allows you to copy the log backups to one or more folders, usually network shares on the standby box.

    To restore the log backup, SQL Backup allows you to restore multiple log files in order, using pattern matching file names. E.g.

    exec master..sqlbackup '-sql "RESTORE LOG pubs FROM DISK = [e:\logs\pubs\logs_pubs_*.sqb] WITH MOVETO = [e:\logs\pubs\restored] " '

    This will pick up all files matching the pattern 'logs_pubs_*.sqb, sort them in the right order, restore them in turn, and move the restored logs to the 'e:\logs\pubs\restored' folder, so that they do not get processed on subsequent runs.

    The log shipping wizard in SQL Backup basically helps you set up the above 3 steps. It essentially does the following:

    - backup the source database (optional)
    - restore the database on the standby box (optional)
    - create a SQL Agent job on the source box to backup the transaction logs periodically
    - create a SQL Agent job on the standby box to periodically restore the transaction logs

    If you can reach the standby box from your source box, you can still use the log shipping wizard to set up the jobs, but you would need to modify the BACKUP job that is created, to remove the COPYTO values as you will be transferring the files via ftp.

    If however, you have no access to the standby box, you would need to set up the jobs manually, which isn't difficult.

    On your source box, set up a SQL Agent job to backup the logs periodically. You can use the 'Schedule Backup' wizard to do this. On your standby box, set up a SQL Agent job to perform the restores using a command similar to the example above i.e. use the appropriate search pattern, and move the restored files to a different folder. That is essentially it.

    >> Do I need to setup a log shipping job on source machine even though there isn't a local destination machine?

    There isn't really a 'log shipping job'. It is really a normal transaction log backup job that you need to set up.

    >> Do I need to create the the identical share names on the real destination server and copy the log files there?

    No. SQL Backup picks up the files from the folder you specify in the DISK parameters, and you can change this to fit your needs.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I will give this a try on a smaller database and see how it goes.

    One last question...

    Once I start the log shipping, can I ever do a full backup on the source machine without throwing off the log sequence? If I do a full backup, do I have to do a full restore on the destination?

    Thanks,
    Matt
    Matt Dolan
    T-Systems International
  • Options
    peteypetey Posts: 2,358 New member
    A full backup does not break the transaction log backup sequence. The log backup made subsequent to the full backup will still contain the log entries from the time of the previous log backup to the present moment. Thus, you do not have to perform a full database restore.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thank You.
    I will gice it a try.
    Matt Dolan
    T-Systems International
Sign In or Register to comment.