-
Written By Khushboo Maurya
-
Updated on October 16th, 2026
How to Fix SQL Server Error 3159: Causes and Solutions
Fact-checked and reviewed in accordance with our
Editorial Policy
You start a database restore in SQL Server & expect it to finish in a few minutes. Instead, it stops with a message about the “tail of the log.” This is SQL Server Error 3159. It looks alarming, but it is a safety check built into SQL Server, & it is usually easy to fix. This guide explains what the error means, why it appears, & how to resolve it without any loss of data.
What Is Error 3159 SQL Server?
The full message reads:
Msg 3159, Level 16, State 1: 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.
It is normally followed by Msg 3013: RESTORE DATABASE is terminating abnormally. In short, SQL Server has found transactions in the live transaction log that have not been backed up. Restoring over the database would destroy them permanently, so the engine refuses to continue.
Microsoft documents this error on its official page: MSSQLSERVER_3159 – Database Engine error.
Common Causes of SQL Server Error 3159
- Restoring over an existing database that has active, un-backed-up log records.
- Full or Bulk-Logged recovery model on the target database. In these models, the log keeps transactions until a log backup is taken.
- No tail-log backup taken before starting the restore.
- Restoring a different backup (for example, from another server) onto a database with the same name.
- Restoring from SSMS with the default option “Take tail-log backup before restore” enabled, while the log backup cannot complete.
- A damaged or inaccessible log file, so SQL Server cannot back up the tail at all.
Before You Fix SQL Error 3159: Know What the Tail Log Is
The tail of the log is the part of the transaction log that has changed since the last log backup. Microsoft explains it in detail in Tail-log backups (SQL Server). It matters because it is what makes point-in-time recovery possible. Also review Recovery models to understand why the Full and Bulk-Logged models trigger this check while Simple does not.
Methods to Fix SQL Server Error 3159
There are many ways through which you can fix the SQL Server Error 3159.
Method 01: Take a Tail-Log Backup, Then Restore (Safest)
Use this when the data written since your last backup matters.
|
BACKUP LOG [YourDatabase] TO DISK = N’D:\Backup\YourDatabase_Tail.trn’ WITH NORECOVERY, NO_TRUNCATE; |
Then run your restore as usual:
|
RESTORE DATABASE [YourDatabase] FROM DISK = N’D:\Backup\YourDatabase_Full.bak’ WITH NORECOVERY; RESTORE LOG [YourDatabase] FROM DISK = N’D:\Backup\YourDatabase_Tail.trn’ WITH RECOVERY; |
NORECOVERY leaves the database in the restoring state so no further changes slip in. NO_TRUNCATE lets the backup run even if the database is damaged. See the BACKUP syntax reference for details.
Method 02: Use WITH REPLACE (When You Don’t Need the Tail)
If the current database is disposable, such as a test or dev copy, you can skip the tail-log backup:
|
RESTORE DATABASE [YourDatabase] FROM DISK = N’D:\Backup\YourDatabase_Full.bak’ WITH REPLACE, RECOVERY; |
Warning: WITH REPLACE overwrites the existing database and discards the unbacked-up log. This cannot be undone. Use it only when you are sure nothing valuable will be lost. The RESTORE arguments page lists every option.
Method 03: Fix SQL Server Error 3159 Through SSMS
- Right-click the database in Object Explorer and choose Tasks >> Restore >> Database.
- Select your backup file or device.
- Open the Options page.
- Either tick Take tail-log backup before restore and set a backup path, or tick Overwrite the existing database (WITH REPLACE) if the data is expendable.
- Click OK.
Method 04: Restore Under a New Database Name
If you want to keep the current database untouched, restore the backup as a new database:
|
RESTORE DATABASE [YourDatabase_Restored] FROM DISK = N’D:\Backup\YourDatabase_Full.bak’ WITH MOVE ‘YourDatabase’ TO ‘D:\Data\YourDatabase_Restored.mdf’, MOVE ‘YourDatabase_log’ TO ‘D:\Data\YourDatabase_Restored_log.ldf’, RECOVERY; |
Use RESTORE FILELISTONLY first to confirm the logical file names in your backup.
Method 05: Use STOPAT for Point-in-Time Recovery
If you only need the database as it was at a specific moment, then the STOPAT clause lets you restore to that time. The message itself mentions it as an alternative to WITH REPLACE.
When the Manual Fixes Don’t Work: Professional Solution
Sometimes SQL Server Error 3159 is a symptom of a bigger problem. The log file may be corrupt, the backup file may be unreadable, or the MDF file may be damaged. In those cases the tail-log backup fails, NO_TRUNCATE does not help, and restoring may end in further errors. Rewriting the database with WITH REPLACE is risky, because you could lose the only readable copy.
This is where a dedicated recovery tool helps. Cigati SQL Recovery Software is built to repair corrupt or inaccessible SQL Server database files (MDF and NDF) and recover their contents without needing a working log.
Key capabilities:
- Recovers tables, views, stored procedures, triggers, functions, rules, & indexes from damaged MDF/NDF files.
- Offers a preview of the recoverable objects before you save anything.
- Can export the recovered data directly to a live SQL Server database or save it as SQL scripts.
- Works without SQL Server being installed on the machine in many scenarios, so no technical setup is needed beyond running the tool.
- Runs on Windows 10, Windows 11, and earlier Windows versions.
- You can download Cigati’s software from the official Microsoft Store.
Basic steps to fix SQL Server 3159 Error:
- Download and install Cigati SQL Recovery Software.
- Click Open and select the damaged MDF (and NDF) file.
- Choose the scan mode and let the tool scan the file.
- Preview the recovered database objects.
- Click Export, select SQL Server Database or SQL Scripts, enter the server details, & complete the export.
- Always keep a copy of the original MDF file before running any recovery, manual or automated.
For the complete process, look at the step-by-step guide for the Cigati SQL Recovery Software.
Tips to Prevent Error 3159
- Schedule regular transaction log backups on databases through the Full recovery model.
- Always take a tail-log backup before restoring over a live database.
- Test restores on a staging server before running them in production.
- Monitor disk space so log backups never fail because of lack of storage.
- Use RESTORE VERIFYONLY to check backup integrity before you rely on it.
Conclusion
SQL Server Error 3159 does not mean your data is gone. It is a warning that a restore would erase work that was never backed up. In most cases a tail-log backup, or WITH REPLACE when the data is expendable, resolves it within minutes. If corruption is part of the picture, a recovery tool like Cigati SQL Recovery Software offers a safer route to getting your data back than forcing the restore.
Frequently Asked Questions
Q1. How to fix SQL Error 3159 without losing database data?
Ans. Take a tail-log backup with BACKUP LOG … WITH NORECOVERY before restoring, then restore the full backup followed by the tail-log backup. Avoid WITH REPLACE, since it discards the unbacked-up log. If the log or MDF is corrupt, recover the data with Cigati SQL Recovery Software first.
Q2. What are the common causes of SQL Server 3159 error?
Ans. The most common causes are restoring over an existing database without backing up its tail log, through the Full or Bulk-Logged recovery model, restoring a backup from another server onto a same-named database, and a damaged log file that blocks the tail-log backup.
Q3. Can I fix SQL Server Error 3159 without technical skills?
Ans. Yes, in cases. SSMS allows you to check the option “Take tail-log backup before restore” or “Overwrite the existing database” with a few clicks. For files the Cigati SQL Recovery Software offers a simple step- by-step interface that does not require any T-SQL knowledge.
Q4. Can I fix SQL Server Error 3159 on Windows 10 & Windows 11?
Ans. Yes. This error comes from the SQL Server Database Engine, not the operating system, so the same fixes work on Windows 10, Windows 11, & Windows Server. The Cigati tool also runs on these Windows versions.
Q5. Why does the tail-log backup fail with “database is not accessible”?
Ans. This usually happens when the log file (LDF) is missing, corrupt, or on a drive that is unavailable, or when the database is in the SUSPECT or RECOVERY_PENDING state. In these cases a normal log backup cannot run. Try BACKUP LOG … WITH NO_TRUNCATE. If that also fails, the log is unusable, and you need to recover the data from the MDF file using a tool such as Cigati SQL Recovery Software.
Q6. Does Error 3159 appear in SSMS restores as well as T-SQL restores?
Ans. Yes. The Restore Database wizard runs the same RESTORE command underneath. SSMS shows the same message if the “Take tail-log backup before restore” option is cleared or if the tail-log backup step fails. Selecting “Overwrite the existing database (WITH REPLACE)” on the Options page bypasses it.
Q7. Can I restore a database in a Simple recovery model without this error?
Ans. In most cases, yes. Because Simple recovery does not preserve log records for log backups, SQL Server has no tail to protect. But if the database was switched from Full to Simple recently, make sure no important log records remain un-backed-up before you proceed.
Q8. Can Error 3159 cause data loss if I ignore it and use WITH REPLACE?
Ans. Yes. Any transactions committed after your last log backup are permanently lost once WITH REPLACE overwrites the database. The data is not recoverable from the restored database. Only use it when you are certain the current database does not hold anything you need.
About The Author:
Khushboo Maurya is a digital content and SEO professional focused on creating useful, search-friendly content that connects with the right audience. She specializes in content optimization, website growth, and practical SEO strategies that improve visibility, engagement, and organic reach.
Related Post
