Latest version: 7.2
Notes & articles
Using SQL Backup log shipping to maintain a standby server
This article provides an overview of log shipping, when to use it, and how Red Gate's SQL Backup simplifies the process. Some more detailed technical information about setting up log shipping using SQL Backup is then provided.
What is log shipping?
Transaction log backups are performed on the database on the primary server (for example the production database).
The backup files are copied to a shared folder that can be accessed by both servers.
The backup files are restored to the secondary database on the standby server
When to use log shipping
You can use log shipping when you want to maintain a standby server but you do not require automatic failover.
Replication is another alternative for maintaining a standby server without automatic failover. For a detailed comparison of log shipping and replication to help you decide which is most appropriate for your requirements, see Log Shipping vs Replication by Paul Ibison on SQLServerCentral.com.
Alternatively, you could use a failover clustering solution. This has the advantage of providing automatic failover. However, it is more difficult than log shipping to set up, requires more expensive licenses, and has other limitations such as on the locations of the servers.
Database mirroring provides another alternative, but it does not support multiple standby databases (unless it is used in conjunction with log shipping).
For more information, see Configuring High Availability in SQL Server Books Online.
Why use SQL Backup for log shipping?
Using SQL Backup makes log shipping much simpler compared with using native log shipping.
The SQL Backup graphical user interface provides a Log Shipping wizard to assist you with setting up the log shipping. The wizard guides you through the configuration.
Then, when you have reviewed a summary of the configuration, the Log Shipping wizard can automatically make a full database backup of the primary database and restore this backup on the standby database. This is to ensure that the standby database is consistent with the primary database and in the correct state for restoring the transaction log backups. If you have already set up the standby database to accept transaction log backups, you can choose to omit this initial backup and restore step (SQL Backup 6 only).
The wizard then sets up SQL Server Agent jobs that use SQL Backup to perform the backup and restore operations. A SQL Server Agent job is created on the primary server to periodically back up the transaction logs and copy the backup files to the shared folder; another job is created on the standby server to periodically retrieve the backup files from the shared folder and restore the transaction logs.
(SQL Backup 6 Pro edition only) Once log shipping has started, SQL Backup's network resilience functionality helps to protect against both transient and extended network outages that might otherwise cause log shipping to fail. See What happens during log shipping later in this article.
As well as simplifying the configuration process and protecting against network outages, SQL Backup is up to ten times faster than native backup and restore. You can also compress the backups to save space, and encrypt the backups for added security.
To perform log shipping using the SQL Backup Log Shipping wizard:
- You must have at least two SQL Server database engine servers or two database engine instances in your log shipping implementation.
Note that you cannot ship transaction logs between SQL Server databases if the destination database is running on an earlier version of SQL Server than the source database (for example, from a SQL Server 2005 database to a SQL Server 2000 database).
You can ship transaction logs between SQL Server databases if the destination database is running on a later version of SQL Server than the source database (for example, from a SQL Server 2005 database to a SQL Server 2008 database), but you must use No Recovery Mode. See Choosing the Log Shipping Mode later in this article.
- SQL Backup Pro server components must be installed on both of the SQL Servers.
Note that if the source database is running on an instance that has SQL Backup Pro v6 server components installed, the destination database must also be running on an instance that has SQL Backup Pro v6 server components installed to be able to automatically process the incoming transaction log files.
- The database must use the full or bulk-logged recovery model.
- You must have a shared folder to copy the transaction log backups to.
The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder. The SQL Server Agent account of the standby server must have read and delete access to the shared folder.
- The SQL Server Agent services must be running and configured with network credentials (such as a domain account) if you plan to use a network share as the shared folder.
You can configure log shipping with SQL Server Agent services stopped, but the process does not run until the agent is started.
To use SQL Backup, the startup user for the SQL Backup Agent Service application must be a member of the SQL Server sysadmin fixed server role and have the following permissions:
- access to the shared folder
- the usual SQL Server backup permissions
- execute permissions on the SQL Backup extended stored procedure sqlbackup (for example, must be a member of the public database role in the master database)
- execute permissions on the following extended stored procedures:
The user configuring the log shipping process must have sysadmin access to the participating servers.
Choosing the log shipping mode
When you use the SQL Backup Log Shipping wizard, you select the mode on Step 3 under Recovery completion state. The log shipping mode you select determines which operational requirement you want to use the technology for.
If you want to use the log shipping configuration only for the standby server, choose Non-operational (No Recovery Mode); the standby database will not be available for users to query.
To allow users read-only access to the standby database between transaction log restores, choose Read only (Standby Mode); this means you can distribute queries across the standby server to reduce the primary server's workload (but you cannot modify the data in the standby database).
What happens during log shipping?
When log shipping is running, the SQL Server Agent backup job periodically makes a backup of the transaction logs on the primary server using SQL Backup. Each backup file is given a unique name.
The transaction log backup files are then copied to the shared folder.
The SQL Server Agent restore job periodically restores the completed transaction log backup files on the shared folder to the standby server using SQL Backup. SQL Backup identifies backup sets (or split backups), ensures all members of a backup set are present, arranges the files in sequence, and then restores each backup set sequentially.
The transaction log backup files that have been restored are then moved from the shared folder to a location that you specify for processed backups when you configure the log shipping so that they are not picked up when the restore job is next run.
Network resilience (SQL Backup 6 Pro edition only)
Once log shipping has started, SQL Backup's network resilience functionality helps to protect against both temporary and extended network outages.
While a transaction log backup file is being written or copied, SQL Backup will retry any failed read or write operations, according to a schedule you specify. This means that temporary network outages should not cause these operations to fail.
If a transaction log backup file cannot be copied to the shared folder (for example, due to an extended network outage), SQL Backup will automatically attempt to copy the file again at regular intervals over the following 24 hours. Subsequent transaction log backup files are queued for copying to the shared folder in the correct order. SQL Backup's intelligent monitoring of the copying process helps to keep log shipping working correctly, even when you experience prolonged network problems.
Customizing log shipping
The SQL Backup Log Shipping wizard provides an easy, step-by-step way to set up log shipping, but it may not provide all the options you need. If required, you can use the wizard to set up the jobs, and then manually modify the scripts that are contained in the backup and restore jobs to suit your needs.
For more information about customizing the SQL Backup jobs, see the SQL Backup Toolkit section of the SQL Backup help.
Monitoring the log shipping
You can use the free Log Shipping Monitor tool for collating and summarizing SQL Server log shipping activity.
The Log Shipping Monitor provides customizable alerts for a variety of key log shipping variables, allowing you to take proactive steps to avoid any log shipping failures. For example, you can set up an alert to notify you if the free space on a drive falls below a specified size, or if no transaction log backups have been created or restored on the standby server for a specified period of time.
In the event a log shipping process should fail, you can use the information provided by the Log Shipping Monitor to identify possible causes of the failure so that you can take the appropriate action.
You can download the Log Shipping Monitor from http://labs.red-gate.com/index.php/Log_Shipping_Monitor
If you need to switch from the primary database to a standby database, you must bring the standby database online manually; there is no automatic failover with the log shipping configuration.
The standby server will be inaccessible or read-only (depending upon whether you chose No Recovery Mode or Standby Mode). To make the standby database available for reading and writing, the log shipping needs to be broken and the database brought online
- If possible, run the SQL Server Agent backup job on the primary server to perform a final log backup on the source database.
If the source database is damaged, a final log backup may fail. In this case, append
NO TRUNCATEto the
BACKUP LOGcommand in the SQL Server Agent backup job, and try running the job again.
- If there are transaction log backup files remaining in the log shipping shared folder, use SQL Backup to restore the backups to the standby database, specifying the option
WITH RECOVERYwhen you restore the final backup.
If there are no transaction log backup files remaining, recover the standby database using the native SQL Server RESTORE statement, for example:
RESTORE DATABASE [database_name] WITH RECOVERY
- Reconfigure connecting resources to use the standby database.
If any SQL users were transferred via the transaction log backups, they will either not exist as logins on the standby server or they will use invalid security identifiers (SIDs) from the source server's login. To reconcile the SIDs:
- Make a list of the users in the database's users container.
- Open the security container and ensure that the users have corresponding logins on the server.
- Reconcile all database users against the security identifier on the standby server using this query:
exec sp_change_users_login 'Update_One', 'MyDBUser', 'MyDBLogin'
where Update_One links the specified user (MyDBUser) in the database (MyDatabase) to the existing SQL Server login (MyDBLogin). The user and the login are usually the same. For more information about sp_change_users_login, refer to SQL Server Books Online.
Note that bringing the database online breaks the log shipping. To re-establish log shipping, you must restore the standby database from a new full backup.
Log shipping provides a relatively easy way to keep a standby database in step with a production database, in case the production database becomes unavailable. It does not, however, provide automatic failover.
SQL Backup provides a wizard to guide you through the configuration, and to set up the SQL Server Agent jobs. Using SQL Backup and the Log Shipping Monitor, you can monitor the log shipping so that you can deal with any potential problems before they arise.
Was this article helpful?
Thanks for your feedback!
- Backups stop working when the SQL account passwords are changed
- Backups and restores taking longer each time they run
- Improving performance by leveraging multi-processor hardware
- Checking the status of a backup or restore
- SQL Backup Agent doesn't start on named instance
- No backup files are erased when the verify option is also used
- Log shipping initialisation failing with error 3176
- Installing SQL Backup unattended
- Issues faced when upgrading an MSCS cluster server
- Cannot schedule log shipping on case-sensitive servers
- Activation fails because response does not match registration properties
- SQL error -1 on backup with 64-bit
- Could not find procedure 'master...sqbutility'
- 'Does not exist' error manually creating a generic service resource
- Backing up log shipping standby databases
- Stopping a backup or restore whilst in progress
- Backing up full-text catalogs using SQL Backup
- Cannot resolve collation conflict
- Microsoft cluster installation (version 4)
- Informing the SQL Agent of job failures
- Database log files may continue to grow even though transaction logs are backed up
- Cannot load xp_sqlbackup.dll
- Minimum Windows Rights Required for SQL Backup Agent
- Bringing a log shipping standby database online
- Console does not support editing the command you selected
- Malformed database disk image error
- Cannot use a local account to run SQL Backup Agent on cluster
- Preparing SQL Servers for remote installation
- SQL error 18210 and VDI error 1010
- Log shipping jobs not deleting old .sqb files
- Scheduled backups may not include new databases
- VDI error 1010 due to an abort request
- Cannot save or see backup templates
- Not all databases appear in the log shipping wizard
- No mapping between account names and security IDs was done
- SQL error 3101 (database is in use) during log shipping
- Insufficient quota to complete the requested service
- Configuring SQL Server procedure memory
- Backing up and restoring on SQL Server Express Edition
- Cannot run two database backups at the same time
- Restoring database backups across a local network
- In some situations the last compressed data block fails to be written to disk
- Configuring permissions for the SQL Backup Agent service
- Restoring backups from one server to a second server using the user interface
- Using SQL Backup tags to automate backup file naming
- VDI 1010 Error
- SQL Backup 5 cannot connect to servers running SQL Backup 4.0
- Files are created when NOWRITE or NOCOMPRESSWRITE is used in conjunction with multiple threads
- Cannot backup a database when full-text catalogs are not online
- Moving a full-text catalog on RESTORE
- Preventing backups over the network from timing out
- Deleting backup history manually
- SQL Backup installation from SQL Toolbelt installer only installs the console
- Console taking a long time to start up
- Reporting across multiple servers, login fails for user NT AUTHORITY\ANONYMOUS LOGON
- SQL Backup Agent cannot start due to account lookup error
- IO Error on backup or restore restart-checkpoint file
- Unable to edit SQL Backup Scheduled Jobs via the Jobs Tab in the SQL Backup GUI (version 5)
- VDI error 1000 caused by improperly installed VDI interface
- VDI Error 1000 caused by account lookup failure
- VDI error 1000 caused by insufficient permissions
- Error 193: %1 is not a valid Win32 application.
- Installing SQL Backup on a cluster
- Restoring the SQL Server MASTER database
- VDI Error 1010: Failed to get the configuration from the server because the timeout interval has elapsed
- Changing the default data location used by SQL Backup
- Index was outside the bounds of the array error during registration
- Repairing the SQL Backup server-side data store
- Troubleshooting slow backup and restore tasks
- Completed backup jobs showing "in progress" status (version 5)
- Restore wizard: files incorrectly labeled 'Missing' when browsing for a backup file to restore.
- When creating a backup job using the wizards some of the user databases may not be listed
- How to restore a filegroup
- System error 1453 while backing up (Insufficient quota to complete the requested service)
- SQL Backup VDI 1030: Failed to create VirtualServiceSet component
- Backups fail on named instances on custom ports
- SQL error 3101 (database is in use) during full database restore
- Error acquiring mutex occurring during use
- Transaction log continually growing on disaster recovery database
- Reseeding a disaster recovery database
- SMTP mail options not being saved
- SQL Backup Activation NullReferenceException Error
- Scheduling SQL Backup restore jobs
- SQL Backup Error 880: permission denied in database
- Deleting remote backup files after a specified time period (version 5)
- SQL Backup Unable to Login to Perform Backup or Restore
- Restore operation errors with exitcode 680
- "Failed to initialize local data store" error
- Trouble browsing network shares when backing up or restoring
- System error 32 (The process cannot access the file because it is being used by another process)
- Log backup failing because there is no current database backup
- Error locating server or instance of SQL Server
- How to set up log shipping between machines on different domains
- SQL Backup and CPU affinity
- SQL Backup mirrored backups
- Error trying to run backup job
- "Access to the database file is not allowed" error
- The server principal NT AUTHORITY\SYSTEM is not able to access the database
- Browsing while using SQL Server authentication is disabled on the selected server
- Cannot generate SSPI context message
- Log ship to multiple servers
- File browser does not work for remote servers
- Configuring Log Shipping to two Target Databases
- Previous backups not deleted according to ERASEFILES setting
- SQL Backup Reporting fails after upgrading to Version 6 from Version 5 for Multiple
- VDI error 1010 error on log backup when no backup is available
- SQL Backup failed with exit code: 1010 SQL error code: 3101 [SQLSTATE 42000] (Error 50000). The step failed.
- Log backup for a database failing to get copied to the log shipping share
- Mirrorfile backup jobs producing corrupt files
- Activity monitor showing sqlbackup status as wait type MSQL_XP
- Backup and restore processes using high percentage of CPU resources
- Warning 485: File does not exist encountered during a restore (but the process completes successfully)
- Log Shipping error 130 when moving restored file
- SQL Backup "Warning 110" generated
- Attempting to restore data into a SQL Express database that exceeds limit
- SQL error 3241 when restoring database
- A nonrecoverable I/O error encountered on Backup or restore operation
- SQL error 15404
- General log-in problem: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
- Warning: System error 1450 when Backing up
- Problems with "Auto-Close" option set when backing up
- Warning: System error 64 (The specified network name is no longer available) on backing up across a network
- SQL error 4208 encountered on backing up
- Warning 170: "Log files are not in sequence" - on log shipping (restore) operation
- Warning: System error 121 (The semaphore timeout period has expired)
- Changing your participation in the quality improvement program
- Installing SQL Backup server components unattended
- Changing your participation in the quality improvement program
- Verifying backups of the master database
- Log files
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
- Getting help offline
- Using SQL Backup to back up to a network share
- Manually installing SQL Backup server components on clusters (Windows Server 2003)
- Manually installing SQL Backup server components on clusters (Windows Server 2008)
- Using SQL Backup log shipping to maintain a standby server
- SQL Backup release notes - version 6.xx
- SQL Backup release notes - version 7.xx
- Working with the new features in SQL Backup 6
- Using flexible licensing with SQL Backup Pro and SQL HyperBac
- Using SQL Backup Pro and SQL HyperBac together on the same server
all SQL products
- Red Gate product acknowledgements
- Activating your products
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs