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."

  • do you have a cleanup task also running? Dec 8 '12 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"? Dec 10 '12 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
    Dec 11 '12 at 19:52
  • I didn't have a cleanup task running (which I should probably start doing...).
    – mcfea
    Dec 11 '12 at 19:52

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.

  • Remember that you can right click on the database, go to reports, and backup history. You can also view the scripts from this site to query them manually. This way you will know if SQL Server at least attempted the backup. sqlbackuprestore.com/backuprestorehistorytables.htm Dec 11 '12 at 19:58

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.