Resolving Error reporting Msg 3159, Level 16, State 1, Line 1 while attempting to restore a database
While you attempt to restore a database which is in Full or Bulked Recover, you may receive below error which terminates your restore command:
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database " " 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.
This is actually a feature of SQL Server which prevents you to replace an existing database accidentally without taking a tail log backup. You can take one of the below approach to resolve it:
Approach 1: It is always recommendable to take a tail log backup before restring a backup over it. Tail log backup is nothing but taking a log backup and then put the database in restoring mode. (This will guarantee that no one can do anything on that database after you took last tail log backup).
Please refer to http://blog.consultdba.com/2010/08/tail-log-backup-what-is-it-and-how-to.html to view the syntax and other details on tail log backup.
Approach 2: If you are sure not to care about existing database, then instruct SQL Server specifically to overwrite the existing database while writing the restore command. Please follow below syntax for the same:
RESTORE DATABASE [DatabaseName]
FROM DISK = N'[Backup File localtion]'
-- Change files name/location and other details as needed
WITH FILE = 1,
NOUNLOAD, REPLACE, STATS = 10
GO
Comments