Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
  • back up to: Disk
  • Back up 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."


Thanks in advance for your time.

share|improve this question

migrated from stackoverflow.com Dec 8 '12 at 3:34

This question came from our site for professional and enthusiast programmers.

    
do you have a cleanup task also running? –  Mitch Wheat 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"? –  cfradenburg 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

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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 –  Ali Razeghi Dec 11 '12 at 19:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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