Is that possible to make backup to any specific date in the past in SQL Server?

For example I have full db backup for 15:30, then at 17:30 there were unintended actions on db: is there a way to backup changes made from 15:30 to 16:30?

  • The Simple answer is NO. – M.Ali Dec 29 '16 at 0:54
  • This is why log files exist. You can RESTORE to 16:30 if you make a log file backup at any time after 16:30, AND you use full logging. If you use simple logging, you can't do this. So, in a normal deployment on major databases, you will make log file backups every (15 min / 30 min / hour) depending on your tolerance for loss and your activity levels. If the problem was a bad query, you can even take a log backup after the accident, then restore to a specified time. (This doesn't work if the disk broke, though). – Laughing Vergil Dec 29 '16 at 1:28
  • @LaughingVergil, how to perform such restore ? And how to know if the server is configured to make log file backups? – R. Matveev Dec 29 '16 at 1:44
  • @M.Ali And the Full answer is MAYBE. – Zev Spitz Dec 29 '16 at 8:48
  • If this query returns SIMPLE, your pretty much out of luck - select recovery_model_desc from sys.databases where name='<YourDatabase>' – Scott Hodgin - Retired Dec 29 '16 at 10:37

If your database is configured for SIMPLE recover model, you're pretty much out of luck.

If your database is configured for FULL recovery model, you might be in luck. This query will tell you what the recovery model is:

select recovery_model_desc from sys.databases where name='<YourDatabase>'

If your database is configured for FULL recovery, you could attempt to take a transaction log backup (example here). Then, you could attempt to restore your full backup WITH NORECOVERY and then restore your transaction log backup to a point-in-time using the STOPAT option (example here)

Assuming you can attempt a restore to point-in-time, you should probably restore to a test database to prevent overlaying the current one and then retrieve the missing data from the test database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.