Log shipping

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

Log shipping is a term used to describe the process of taking a transaction log backup from a primary database, and restoring the transaction log backup on a secondary database.  Why would you want to do this?  Among the more common reasons:

·to have a standby database ready in case of a service disruption to the primary database

·to have a read-only copy of the primary database available on another server, in order to lighten the load on the primary server

·to have multiple copies of the database available in different locations

   Log shipping overview   


The backup is the easy part.  You just need to ensure that your database is running the full or bulk-logged recovery models.  It's the restore part that gets a little tricky (I'm working on the common assumption that the secondary server is a physically different server).  Like:

·how can the secondary server access the backup file

·how will the secondary server know which files to restore

·how will the secondary server know which files not to restore, as they may be in the process of being copied

SQL Server provides a GUI interface to assist you in setting up log shipping between a primary database and one or more secondary databases.  However, it is only available in certain editions of SQL Server.  In SQL Server 2000, it is available only in the Developer and Enterprise editions.  In SQL Server 2005, it is available in all editions except the Express edition.

Using the SQL Server log shipping configuration may not always be suitable, for e.g. if the primary and secondary databases are not linked.  You can always set up log shipping manually, but you'll need to address the abovementioned issues.

While it is possible to ship logs from a SQL Server 2000 database to a SQL Server 2005 instance by setting up log shipping yourself, the database can only be in a recovery state, not in a read-only state.  This is because when SQL Server 2005 makes the database read-only, it upgrades the database's internal structures too, making it impossible to apply further non-SQL 2005 transaction logs.


Another consideration when performing log shipping is that the logins on the primary database needs to be synchronised with the secondary database when failover occurs.  BOL suggests using the bcp function to periodically export the master..syslogins table (SQL 2000), or sys.syslogins (SQL 2005).  During failover, you will need to use the sp_resolve_logins stored procedure to resolve the logins.  After that, you may need to relink orphaned users using the sp_change_users_login stored procedure.

You can find more details on how to resolve orphaned users in Troubleshooting Orphaned Users [SQL2000, SQL2005].

Document history
6/27/2008    Initial release    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.