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

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

July 3, 2009 IT Support 18 Comments

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

Related Posts

18 Comments

  1. Vijay March 24, 2010

    hey cool….. very informative.. Its working for me.

    Thanks

  2. Nurlan March 30, 2010

    =) Nice. It helped me =)

  3. anurag April 28, 2010

    thank for for …infomation
    now i got the answer
    tank u..

  4. lalit June 4, 2010

    i face error of this type anyone know plz. help
    system.data.sqlclient.sqlerror:the media set has 2 media families but only 1 are provided.all members must be provided(Microsoft.sqlserver.smo)

  6. zaheer June 7, 2010

    Cool, informative. Thanks

  7. Manas August 2, 2010

    Its coollll man.
    its workign for me.

    Thanks for this post.

  8. LxZv October 1, 2010

    thanks 🙂

  9. jaypee September 21, 2011

    it’s working… thank you so much…

  10. Ravi January 3, 2012

    hi i tried to restore the database in my laptop sqlserver 2005 databse ,i followed the above procedure it wont work plz help ….
    still iam facing the problem

  11. Rajarathinam May 10, 2012

    It is very usefull to me. I soent for nearly three hours

  12. David A May 31, 2012

    Brilliant! Thank you sooooooooooo much.

  13. ali July 30, 2012

    thanks brother 🙂

  14. RAgesh` November 28, 2012

    Hi, It is not solved for me.

    Anybody can assist me. I’ve got the following error even I’ve checked the Replace db option

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    Restore failed for Server ‘USER’. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  15. Vishy March 16, 2013

    Many Many Thanks !
    This Worked for me !
    Was struggling since hours….
    Just a Click…. Wonders

  16. Roja March 21, 2013

    Its not working for me…

  17. vinay krishna September 28, 2013

    System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)
    got this error how to resolve plzz reply soon 🙁

  18. Irfan July 1, 2016

    Thanks it works for me

Leave a Reply