Log Shipping using SQL Backup
HJoyce
Posts: 186
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'.
(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'.
Helen Joyce
SQL Backup Project Manager
Red Gate Software
SQL Backup Project Manager
Red Gate Software
