SQL SERVER – FIX: Msg 4335, Level 16, State 1 – The specified STOPAT time is too early

7 years ago

While trying to prepare for a demo, I ran into some interesting error. When I searched for error about STOPAT on the internet, there were not many clear answers so I thought of sharing on my blog. Actually, my demo was all about point in time restore feature of SQL Server. I was trying something similar given in my earlier blog.

SQL SERVER – A Quick Script for Point in Time Recovery – Back Up and Restore

Here is the error which I received

Msg 4335, Level 16, State 1, Line 3
The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
The error was not very clear so I checked my script and found the mistake. I was giving incorrect time in STOPAT. To understand it better, I have written a small script.

/*Clean up*/
USE [master]
GO
ALTER DATABASE [SQLAuthority] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SQLAuthority]
GO
/*create database*/
CREATE DATABASE [SQLAuthority]
GO
USE [SQLAuthority]
GO
CREATE TABLE FIRST(I INT)
GO
BACKUP DATABASE [SQLAuthority] TO  
DISK = N'E:\Backup\SQLAuth_1.bak' WITH FORMAT, INIT,  
NAME = N'SQLAuthority-Full Database Backup 1', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
SELECT GETDATE() 'Approx Time of first full backup'
GO
CREATE TABLE SECOND(I INT)
GO
WAITFOR delay '00:00:10'
GO
BACKUP DATABASE [SQLAuthority] TO DISK = N'E:\Backup\SQLAuth_2.bak' 
WITH FORMAT, INIT,  NAME = N'SQLAuthority-Full Database Backup 2', SKIP, 
NOREWIND, NOUNLOAD,  STATS = 10
GO
SELECT GETDATE() 'Approx Time of second full backup'
GO
CREATE TABLE THIRD(I INT)
GO
WAITFOR delay '00:00:10'
GO
BACKUP LOG [SQLAuthority] TO DISK = N'E:\Backup\SQLAuth_3.bak' 
WITH FORMAT, INIT,  NAME = N'SQLAuthority-Log Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
SELECT GETDATE() 'Approx Time of log backup'
GO

Here are backups taken via about the script.

Full back up – 1: SQLAuth_1.bak
Full back up – 2: SQLAuth_2.bak
Transaction Log back up: SQLAuth_3.bak

The error would come if I restore SQLAuth_3.bak on top of SQLAuth_2.bak BUT specify time before the SQLAuth_2.bak. I have modified the script to make sure you can understand the cause.

Here is the output of my SQL instance.

If I run below command, I would get the same error which I explained earlier.

USE [master]
GO
IF (DB_ID('SQLAuthority') IS NOT NULL)
BEGIN
ALTER DATABASE [SQLAuthority] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [SQLAuthority]
END
GO
USE [master]
RESTORE DATABASE [SQLAuthority] FROM 
DISK = N'E:\Backup\SQLAuth_2.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [SQLAuthority] FROM  DISK = N'E:\Backup\SQLAuth_3.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 5, 
STOPAT = '2016-01-17 22:05:50.593' --This time has to be modified
GO

In the above example, STOPAT was done at the time when the first full backup was taken. You have to replace the time as per your script execution and give any time before second output.

I am sure such small scripts make concepts very clear. Have you ever encountered any interesting restore error? Please share via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)