Log Shipping on a WAN

mdolan

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?

petey

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.

mdolan

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

petey

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.

mdolan

Thank You.
I will gice it a try.