Sql-server – Restore database to point in time from full backup only

recoveryrecovery-modelrestoresql serversql-server-2005

I have a database with recovery mode set to FULL. It's a dev database and I have two full backups dated 19th and 27th i.e. latest.

One of the developer did some changes on 26th and I want to rollback those changes and want to restore database at a state of 25th. 

restore database MyDb from disk = 'I:\MSSQL\DATA\MyDb_20160127.bak'
with recovery, stopat = '2016-01-25 10:38:15.280',
move 'MyDb' to  'I:\MSSQL\DATA\MyDb.mdf',
move 'MyDb_log' to 'I:\MSSQL\DATA\MyDb.ldf'

The STOPAT clause specifies a point too early to allow this backup set
to be restored. Choose a different stop point or use RESTORE DATABASE
WITH RECOVERY to recover at the current point.

I am using SQL Server 2005

Best Answer

No, you need to have taken transaction logs that cover the time frame you want to use with STOPAT. You can't do this from a full database backup only - that is just a one-time copy, and that is why we have different types of backups (full, log, diff).

If you have taken transaction log backups in between your full backups, please update the question with that information, and we can show you steps for restoring to a point in time. But those tutorials are easy to find, so I suspect you're asking here because you haven't taken log backups. If that is the case, you're kind of stuck - you may be able to use some kind of log reading utility to get at that information from the current transaction log, but I wouldn't be confident about success there - and it doesn't seem like that product, at least, has a feature called "restore to point in time" - it allows you to recover specific pieces of information, like restoring a table or undoing a truncate. So I think that would be quite a manual "undo transaction A, undo transaction B, undo transaction C, etc etc" to get at your eventual result. Probably still less work than trying to do that yourself from fn_dblog output.

This post is rather timely:

And these may be useful too:

To demonstrate that you can't now take a log backup and try to restore to some point in time prior to the last full backup. First, run this script:

USE master;
GO
CREATE DATABASE splunk;
GO
ALTER DATABASE splunk SET RECOVERY FULL;
GO
USE splunk;
GO
SELECT GETDATE(); -- use this in STOPAT
GO
WAITFOR DELAY '00:00:10';
GO
BACKUP DATABASE splunk TO DISK = 'c:\temp\sp_full.bak' WITH INIT;
GO
WAITFOR DELAY '00:00:10';
GO

The above simulates what you have: a full backup, taken after the time you want to restore to, and that's it.

Now, take a tail-log backup:

BACKUP LOG splunk TO DISK = 'c:\temp\sp_tail.trn' WITH INIT, NO_TRUNCATE;
GO

Now, let's start the recovery process. First, restore the full backup with norecovery:

USE master;
GO  
RESTORE DATABASE splunk FROM DISK = 'c:\temp\sp_full.bak' WITH REPLACE, NORECOVERY;
GO

Now, attempt to restore the tail-log backup with the STOPAT parameter set to whatever was output above for GETDATE():

RESTORE LOG splunk FROM DISK = 'c:\temp\sp_tail.trn' 
  WITH NORECOVERY, STOPAT = '<paste GETDATE() output from above>';

I get the following error messages:

Msg 4335, Level 16, State 1
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
RESTORE LOG is terminating abnormally.

In order to restore to a point in time, you need to have taken at least one transaction log backup after a full backup (not necessarily after the last full backup, but after one that is valid and can be restored), and after the point in time you want to restore to (but before the next full database backup).

This is why we typically take full backups nightly or weekly, and transaction log backups every 15 minutes or every hour (and sometimes diff/filegroup backups in there too, depending on business needs, RTO/RPO, etc). In full recovery model there is certainly no use case where you'd want to take a full backup once a week, and nothing in between. This can be a very expensive lesson to learn the hard way, but I bet it doesn't happen to you twice.

This post probably has a lot of useful points about backups, why we take them, the usefulness of log backups, etc. - I highly recommend bookmarking it and giving it a thorough read once you solve your current problem.

Related Question