This month’s T-SQL Tuesday invitation comes from Kevin Chant and he’s asking us about a fantasy feature that we’d love to see in SQL Server.
I had a little think about this and there’s one thing that came straight to my mind, it is something that’s possible with third party tools but isn’t something supported natively by SQL Server.
How often do you have someone come up to you and ask ‘any chance that we can restore so and so table back to last week?’.
The answer is usually a flat no. Ok, that might be a little flippant, I could restore the backup and copy that table out to somewhere appropriate. Perhaps overwriting the data in the existing table or putting it somewhere else, depending on what was needed.
That’s all well and good if we’re talking about a database of a few GB but what if the database that we’re talking about is in the TB? I’m not going to want to go restoring that beastie anywhere.
Wouldn’t it be lovely if SQL Server gave us the option of table level restores?
But why stop there, we could have object level restores.
Someone’s changed the definition of a proc and broken it, well just restore the proc back to an earlier point in time. Views could be treated in the same way or even perhaps users and permissions. Yes, this is something that could be avoided with decent change and version control, but c’mon you and me both know how often that happens…
Obviously we’d need to be very careful here, object level restores could easily land us in hot water with data consistency but wouldn’t it be nice if we had an easy way to step back in time and grab certain a object without having to restore the entire database?