The tail of the log for the database "DBName" has not been backed up

Question

I tried to restore a database using the following query:

ALTER DATABASE [DatabaseName] SET Single_User WITH Rollback Immediate GO
RESTORE DATABASE DatabaseName FROM DISK = 'C:\DBName-Full Database Backup'
ALTER DATABASE [DatabaseName] SET Multi_User GO

but instead of restoring the database, I am getting this error:

Msg 3159, Level 16, State 1, Line 2

The tail of the log for the database "DatabaseName" 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. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

In my case it had happened when I already had a database with same name on the same SQL Server. I just deleted the db with the same name and ran the restore command. Note: I deleted db with the same name since it was an old copy of the db that I did not need anymore. But, if you still need the old copy you may either want to make a backup of it or rename either the old or the new db. But, if you don't have a db with the same name then do what the error message is asking to do. — nam, Oct 01 '17 at 19:26

score 117 · Accepted Answer · edited Jul 10 '13 at 22:04

117

The error message you are getting tells you exactly what you need to do if you don't care about the existing database or log.

RESTORE DATABASE DAtabaseName FROM DISK = 'C:\DBName-Full Database Backup' 
WITH REPLACE

In SQL Server Management Studio (Tasks > Restore), you can add the WITH REPLACE option by opening the page "Options" on the left side and ticking "Overwrite the existing database".

edited Jul 10 '13 at 22:04

AndiDog

68,631
21
159
205

answered Sep 11 '12 at 13:38

tomfanning

9,552
4
50
78

Had a transport issue as well. Had to Run USE master ; ALTER DATABASE RAVEN SET RECOVERY SIMPLE; Just remember to turn it back to FULL when you are done. – Yogurt The Wise Aug 25 '14 at 14:36

score 31 · Answer 2 · answered Jul 25 '14 at 12:25

31

For those who are using Management Studio this should work:

enter image description here

answered Jul 25 '14 at 12:25

Adil Malik

6,279
7
48
77

it essentially only add a [replace] keyword to [With] param list if you usually generate scripts from SSMS – Iman Oct 10 '14 at 07:54
image no longer visible :/ – rpr Oct 31 '19 at 10:24
1

@rpr it's showing OK to me. BTW, I have uploaded it here for you: https://imgur.com/a/ki9elxa – Adil Malik Oct 31 '19 at 14:09

score 7 · Answer 3 · edited Oct 29 '14 at 17:08

7

Alternatively, you can change the database recovery model to Simple instead of Full.

Right click on the database
Choose properties-> option
Change recovery model to simple

Then what you have written should work without producing errors.

It worked good with me.

edited Oct 29 '14 at 17:08

Ram

3,092
10
40
56

answered Oct 29 '14 at 16:40

catcher

71
1
1

score 2 · Answer 4 · edited Feb 19 '13 at 20:13

2

Alternatively, you can change the database recovery model to "Simple" instead of "Full".

Right click on the database, choose 'properties', 'option', change "recovery model" to "simple".

Then what you have written should work without producing errors.

edited Feb 19 '13 at 20:13

Andrew Barber

39,603
20
94
123

answered Feb 19 '13 at 20:01

saeed khalafinejad

1,139
9
22

score 0 · Answer 5 · answered Sep 11 '12 at 13:36

0

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.

Really, that's the answer. Right there, in the message. What do you want to do? Backup the tail so is no lost? Replace the log that was not backed up? Your call.

answered Sep 11 '12 at 13:36

Remus Rusanu

288,378
40
442
569

1

since I work rarely with SQL Server I backup Database by going to tasks, backup and performed a backup, and now want to restore it, but it doesn't let me, so I don't exactly know the reason of above error, and most forums says it works without getting any error – tereško Sep 11 '12 at 13:40
4

The error is warning you that if you continue with the RESTRORE, you will loose the current tail of the log. In other words, some operations were not yet backed up. It gives you two options: either backup the current tail of the log so is poreserved and you can restore it somewhere else or later *or* use the WITH REPLACE clause of RESTORE to instruct SQL Server that you do not care about the operations that would be lost in the current tail of the log and simply go ahead and replace the current content with the one from the backup file you are restoring. – Remus Rusanu Sep 11 '12 at 13:44

The tail of the log for the database "DBName" has not been backed up

5 Answers5