I've created a Database maintenance plan for creating a backup set daily. The Back Up Database Task has three subplans:
- Full Backup: Occurs every day at 12am
- Differential Backup: Occurs every hour starting at 1:30am
- 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.