Restore to point in time -

1

Hello

Let's say I have a db backup (MSSQL - 2014) created on 25.1.2018.
I have another backup done on 30.1.2018.
The recovery model is set to FULL. The last transaction log backup was
crated somewhere in march 2017. Can I use that data to restore
to a point in time on 27.1.2018?

Regards

2

No. A tran log backup can only be applied to the immediately previous full and, optionally, diff backup.

For example, to get to a point-in-time of 27.1.2018, you'd need the full backup from 25.1.2018, and then a tranlog(s) taken after that. Optionally you could use a diff after that, and then use a tranlog(s) after the diff.

Once the next full backup occurs, you can't apply any older diff or tranlog backup on it.

3

If the log has been growing since March 2017 you could.

  1. Create a Transaction Log Backup.
  2. Restore your backup from say 25.01.2018 to a different DB WITH NORECOVERY
  3. Restore from the Transaction Log Backup you have just created to STOPAT say sometime on 27.01.2018.
    This might take sometime if the log goes back to March 2017.
4

It would appear as if that's not possible when a full db backup has been made. The log is still there and growing, but it's useless to restore to a point in time except for time AFTER the last backup. Or at least that's how i understood it. Seems kinda pointless to have that kind of log if that's the case.
I'm sure there's a way to get the data out of it since apparently APEXsql recover does it, but it would appear that's not possible without it (why even include it if it's not possible without third party tools - actually, the onyl one i found is actually ApexSQL).

5

You can use the log to restore to a point after any full backup, as long as the log hasn't been truncated in the mean time. In SQL Server, "truncated" does not mean physically shrunk, it means log records were marked as being free to write over without having backed them up first.

If, for some very unusual reason, your db stayed in full recovery mode, and log file was never backed up since Mar 2017, you could take a new log backup and use that to recover after restoring a full backup. But you can only restore forward from the time of a backup, not backward. (And, btw, it could take a very long time to restore such a vast log file.)

6

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). In order to restore to a point in time, you need to have taken at least one transaction log backup after a full backup, and after the point in time you want to restore to.
If you want some more information related to point in time restore, refer to the following link: