1

I've created a Database maintenance plan for creating a backup set daily. The Back Up Database Task has three subplans:

  1. Full Backup: Occurs every day at 12am
  2. Differential Backup: Occurs every hour starting at 1:30am
  3. Transaction Log Backup: Occurs every 10 minutes starting at 1:45am

The general idea is that we only have to restore the full backup, then the last viable differential, and then the logs after that.

The issue is that the previous days full backup is not available when using the Restore Database tool in SSMS. I.E. if I want to go back more than last nights full backup I'm out of luck.
What detail did I miss here?

It might give a hint if I describe how I discovered this issue... I created a database yesterday afternoon and worked on it into the evening. Today I realized that I'd like to restore back to a point about an hour after I created the database to find that I could only go back to the full backup that was created at midnight (see below for configs if you think that will help...).


Further configurations below:

Full backup settings

  • Connection: Local server connection
  • Backup type: Full
  • Database(s): All databases
  • Backup set will expire: (not selected)
  • Backup to: Disk
  • Backup databases across one or more files: (not selected)
  • Create a backup file for every database: (selected)
  • Create a sub-directory for each database: (checked)
  • Folder: \backups\sql
  • Backup file extension: bak
  • Verify backup integrity: (checked)
  • Set backup compression: Use the default server setting

Differential backup settings

All is the same as full except that type is differential

Transaction Log backup settings

All is the same as full except that type is Transaction log and backup file extension is "trn."

4
  • do you have a cleanup task also running? Commented Dec 8, 2012 at 1:42
  • Are you clicking the button next to where it says, "To a point in time:" so it no longer says "Most recent possible"? Commented Dec 10, 2012 at 13:29
  • I did try "to a point in time." Thanks for the suggestion as I had missed this detail previously. It didn't solve the issue, but it was something I didn't know about before.
    – mcfea
    Commented Dec 11, 2012 at 19:52
  • I didn't have a cleanup task running (which I should probably start doing...).
    – mcfea
    Commented Dec 11, 2012 at 19:52

1 Answer 1

1

Ok, so it turns out I didn't know that I couldn't roll back to an earlier version of my newly created database until a full backup was made. This is why I couldn't see any options to roll back to before midnight in the restore dialogue.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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