Providing a Read Only Database Backup with Log Shipping

A frequent question with log shipping is “can I use the database that’s in restoring mode”?  This will come in handy for reporting, or any other read-only use.  The good news is that yes, you can – to a point.  The main restriction is that you can only restore a log when no one else is in the database.  This means you will have to kill all connections to the database whenever you want to perform your log restores.  You have the choice of either doing this more frequently, allowing you to have more up to date data in the database, or less frequently, and having slightly older data.

To begin with, you need to ensure that your database is in FULL recovery mode, not simple.  Next, take a full backup.  On the other server, restore the full backup WITH NORECOVERY.  From here, you can take log backups from the source server and restore each log file (again WITH NORECOVERY) on the target server.

However, this does not allow us to open the database and poke around, and trying to do so will just get you an error message:

Msg 927, Level 14, State 2, Line 2
Database ‘Northwind’ cannot be opened. It is in the middle of a restore.

The solution is to restore the log with a standby file, eg:

RESTORE LOG Northwind WITH STANDBY = 'E:\Log\StandByFile.stb'

The name of the standby file, also known as the undo file) is not important, but this is where uncommitted transactions are stored.  The database cannot be brought online until uncommitted transactions in the transaction log backup have been rolled back, but new log backups cannot be restored to the standby database unless those uncommitted transactions are still in progress.  Thus, the undo file remembers the state of the transaction log to allow you to restore additional transaction log backups.

If you are taking transaction log backups every 5 minutes, you can store them up, and once an hour disconnect all users from the standby database,  restore all transaction logs to the standby, and then bring the database back into Standby mode.

This technique allows you to have a reporting server with a tried and reliable technology (not that replication or mirroring aren’t), and also provides you with a database that you can bring online very quickly if something happens to the primary database.

Mixing SQL Server 2000 Log Shipping with SQL Server 2005

While you can log ship from SQL Server 2000 to SQL Server 2005, you cannot bring the database to a warm standby.  This is because the database needs to undergo an upgrade to bring it online, and it cannot downgrade back to 2000 to apply the next transaction log.  Trying to restore a log WITH STANDBY on a 2000 database under 2005 will give you this error:

Msg 3180, Level 16, State 2, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

As long as you don’t want to recover the database or use it as a warm standby, you can log ship from 2000 to 2005.  The database will only be upgraded when it is recovered, so you can apply as many transaction log backups as required.  However, once you do bring the database back online and it is upgraded, you won’t be able to take this copy of the database back to SQL Server 2000.  This makes log shipping between 2000 and 2005 a poor solution for backups.  Your only option is to copy the database out of the 2005 database back into a 2000 database.