[Solved] System.Data.SqlClient.SqlError: The tail of the log for the database “dbName” has not been backed up

You have backup a database on SQL Server 2005. Then, you try to restore the backup file on the existing database and receive the error message below:

Restore failed for Server ‘SQL Server name‘. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The tail of the log for the database “dbName” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)
The tail of the log for the database 'dbName' has not been backed up

This error message is likely to be a warning message rather than an error message. It indicates that you are going to restore a database to replace the existing database and some data may be lose. For example, you perform backup on this following schedule:

  • database at 10:00 AM
  • transaction log at 11:00 AM
  • transaction log at 01:00 PM

Now if you are going to restore the backup at 04:00 PM. You will lose any work after 01:00 till 04:00 which is same as the error message above try to warn you.

Solution

To solve the problem, you can either:

  • Replace the existing database with a backup file. This option is fast and easy but you will lose any work after the last backup has been performed.
  • Do backup transaction log again with NORECOVERY option before restoring a database. With NORECOVERY option, the database will be in restoring state and waiting for restoration.

In this post, I will show only the first solution only. For the second solution, see How to: Back Up the Tail of the Transaction Log (SQL Server Management Studio)

Restore by overwrite an existing database

  1. On Restore Database, select Options tab on the left menu. Then, check Overwrite the existing database on Restore options.
    Note: This option will overwrite any existing data on the targeted database.
    Restore Database Options
  2. Try to restore the database, the problem should be gone now.
    Restore Database Successful

18 Comments

  1. Vijay March 24, 2010
  2. Nurlan March 30, 2010
  3. anurag April 28, 2010
  4. lalit June 4, 2010
  5. zaheer June 7, 2010
  6. Manas August 2, 2010
  7. LxZv October 1, 2010
  8. jaypee September 21, 2011
  9. Ravi January 3, 2012
  10. Rajarathinam May 10, 2012
  11. David A May 31, 2012
  12. ali July 30, 2012
  13. RAgesh` November 28, 2012
  14. Vishy March 16, 2013
  15. Roja March 21, 2013
  16. vinay krishna September 28, 2013
  17. Irfan July 1, 2016

Leave a Reply

%d bloggers like this: