I recently wanted a script to tell me that for every database on a given server
What levels of backups I have
How many files would need to be restored to get to the most recent backup state.
The size of all the files I’d need to restore
How up to date this process could get me
For example, if we have a database with the following backup schedule
23:00 - Full Backup
06:00, 12:00, 18:00 - Differential Backups
Every 15 Minutes Log Backup
I wanted to know given the backups I have at the time I run this script how up to date I could restore to and how many files would be involved in the restore. The output of this new procedure looks like this…
This information will only show child items after the last parent item in the chain, for example
Only differential backups created after the last full backup
Only logs backups after the last differential or if there is no differential it will fall back to after the last full
Given this information we can see that to get up to date we need to
Restore 1 Full Backup
Restore 1 Differential Backup
Restore 1 Log Backup
Doing this will get us to within about 15 minutes of where the database currently is, we can see this by the fact our most recent log backup is 15 minutes old.
I’m going to walk through an example of creating some backups and restoring them with this logic. If you want to skip ahead and just get the backup status script then it’s at the bottom of this post.
I’ve created a database called RandomDB, It has no backup history and none are scheduled. The output of sp_BackupStatus now looks like this…
If we then run a full backup then look at sp_BackupStatus again…
Then let’s run a few differential backups mixed in with a load of transaction log backups (Pauses between just to make the information returned from sp_BackupStatus a little clearer)…
Even though we took 7 log backs this script is letting us know that to get to the latest possible version we only need to 1 full, 1 differential and 1 log…
To confirm this is up to date we can also check our Test table that we created right before the last log backup exists…
One of the things I really like about this as at any point in the day I can run sp_BackupStatus and quickly see the total sizes of the backup files I’d need to restore to get to the latest possible point in time, This can also give a good indication as to how long this process would take.
And now for the sp_BackupStatus script…
Disclaimer : This was cobbled together in an evening and probably has all sorts of bugs. I’ve put a version of it in my scripts repository on GitHub (https://github.com/gavdraper/GavinScripts), feel free to submit issues and pull requests there.
I've grown up reading Tom Clancy and probably most of you have at least seen Red October, so this book caught my eye when browsing used books for a recent trip. It's a fairly human look at what's involved in sailing on a Trident missile submarine...
Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.
You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...
Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.