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)
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)
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.
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
- 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.
- Try to restore the database, the problem should be gone now.