Jun 112008

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.

  3 Responses to “Providing a Read Only Database Backup with Log Shipping”

  1. What is a sollution to remove above error.
    Can you please explain step by step?
    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

  2. Hi Mihir,

    Unfortunately, there is not much you can do. You cannot bring a database online using WITH STANDBY unless the transaction log you are restoring was created in the same version of SQL Server. This means that if you have a 2008 RTM server, then you must restore the logs on another 2008 RTM server. The upgrade process (from 2008 to 2008 R2) is a one-way operation, and the UNDO file cannot support this.

    If you cannot use another 2008 RTM instance as your standby, then you may need to investigate a different solution, such as replication or database mirroring snapshots (see http://technet.microsoft.com/en-us/library/ms175511.aspx).

  3. Hi Jim,
    I was able to upgrade to R2 while log shipping was running.
    Run Restore job on secondary server.
    Disable primary server backup and secondary server restore jobs.
    Put databases in offline mode on secondary server.
    Upgrade secondary server to R2 and also primary server to R2.
    Run backup job.Run Restore job.
    One more time Run backup job.Run Restore job.
    Log shipping is working fine and servers are upgraded

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>