Red Gate forums :: View topic - Log Shipping using SQL Backup
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

Log Shipping using SQL Backup

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
HJoyce



Joined: 10 Oct 2005
Posts: 183

PostPosted: Tue Mar 20, 2007 10:58 am    Post subject: Log Shipping using SQL Backup Reply with quote

Log shipping using Red Gate SQL Backup
(updated: March 20, 2007)

This document discusses how log shipping is performed using Red Gate SQL Backup so that users know what is happenning behind the scenes, and are able to customise the tasks to meet their requirements.

Overview
Log shipping involves backing up transaction logs of a production database, and restoring those logs on one or more standby servers. Before you commence log shipping, you need to ensure that the standby database(s) are in a consistent state with the production database. The easiest way to do this is to make a full database backup of the production database and restoring this backup on the standby database(s), using the NORECOVERY or STANDBY options.



Using the Log Shipping Wizard in SQL Backup
The Log Shipping Wizard creates a SQL Server Agent job on the production server to periodically back up the transaction logs, and creates another job on the standby server to periodically restore the transaction logs. The backup files are copied to the standby server by the backup job.



The backup job
The backup job performs the backup according to the schedule you set up. The job step command may look something like this (some options have been omitted to keep the code brief):


EXEC master..sqlbackup N'-sql "BACKUP LOG [logshipdb] TO DISK = ''F:\Backups\LOG_(local)_logshipdb_<DATETIME yyyymmdd_hhnnss>.sqb'' WITH COPYTO = ''\\standby1\logship_logs''


This command backs up the transaction log of the logshipdb database. The <DATETIME ...> tag indicates that the naming convention of the file is dependent upon the time that backup is made. The COPYTO option indicates that upon completion of the backup, the backup file is copied to the '\\standby\logship_logs' network share.



The restore job
The restore job restores the transaction logs on the standby server. The job step command may look something like this:


EXEC master..sqlbackup N'-sql "RESTORE LOG [logshipdb1] FROM DISK = ''G:\logship_logs\LOG_(local)_logshipdb_*.sqb'' WITH NORECOVERY, MOVETO = ''G:\logship_logs\processed'' " '


This command restores the transaction log files found in the 'G:\logship_logs' folder matching the 'LOG_(local)_logshipdb_*.sqb' search pattern. Note that you do not need to specify the exact file names. SQL Backup will simply pick up all files matching the search pattern, sort them in the correct sequence, and restore each file seqentially. The MOVETO option indicates that files that have been restored are to be moved to the 'G:\logship_logs\processed' folder. This is required so that during the next restore task, these files are no longer picked up to be restored.



Summary
That is essentially all there is to log shipping using SQL Backup. Basically, it involves a backup, a copy, a restore and a move. Knowing this, it is easy to modify the process to fit your requirements.

The Log Shipping Wizard itself provides a basic framework in which to set up log shipping, but it may not provide all the options you need. You can always modify the scripts that are contained in the backup and restore jobs to suit your needs.

For example, you may want to skip the backup and copy steps if you back up directly to a location that is accesible by the standby server(s). Or you may want to omit the copy process if you are sending the files over a WAN via FTP, but the rest of the steps would still apply.



Frequently asked questions

How do I activate the standby databases?
Use the native SQL Server RESTORE statement to bring the database online e.g.

RESTORE DATABASE logshipdb_standby WITH RECOVERY


Can I copy the backup files to multiple locations?
Yes, use multiple COPYTO parameters to copy the files to multiple locations e.g.

EXEC master..sqlbackup N'-sql "BACKUP LOG [logshipdb] TO DISK = ''F:\Backups\LOG_(local)_logshipdb_<DATETIME yyyymmdd_hhnnss>.sqb'' WITH COPYTO = ''\\standby1\logship_logs'', COPYTO = ''\\standby2\logship_logs'' " '


Can I use the multiple log restore feature outside of log shipping?
Yes. Use the same syntax as you woud for a transaction log restore, but enter a search pattern that will pick up all the relevant files e.g.

EXEC master..sqlbackup N'-sql "RESTORE LOG [mydb] FROM DISK = ''G:\backups\*.sqb'' WITH RECOVERY " '


Can the multiple log restore feature work with split backups?
Yes. SQL Backup will group files belonging to the same backup sets and restore them as a set. You can mix the type of backup sets (single file or multiple files) and SQL Backup will sort them out for you.


Can the multiple log restore feature pick up files from multiple paths?
Yes. Use multiple DISK options e.g.

EXEC master..sqlbackup N'-sql "RESTORE LOG [mydb] FROM DISK = ''G:\backups1\*.sqb'', DISK = ''G:\backups2\mydb*.sqb'' WITH RECOVERY " '


Can the multiple log restore feature work with encrypted files?Yes. For example, the following command

EXEC master..sqlbackup N'-sql "RESTORE LOG [mydb] FROM DISK = ''G:\backups\*.sqb'' WITH PASSWORD = ''logship'', RECOVERY " '

will restore all matching files that are unencrypted or have been encrypted using the password 'logship'.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group