Two great ideas Jeff, thanks.
Jeff Moden (11/4/2015)
That notwithstanding and depending on the change, I may backup separate tables using SELECT INTO to copy the data to a "safe" database
We do this using
SELECT *
INTO SuitableDatabase.dbo.TEMP_OriginalTableName_yyyymmdd
FROM dbo.OriginalTableName
so that they are all neatly grouped, alphabetically, under TEMP_ and don't pollute the normal namespace and when they have been forgotten!! for long enough we can use the "yyyymmdd" to decide "that can't be needed any more" and DROP it.
We also have a copy of the "money maker" database that is made from the backup immediately after database is backed up to not only provide an online secondary in case all hell breaks loose but also to test the backup. Of course, that database is only 350GB so none of that takes much time.
Great idea, not considered that before. Where diskspace is sufficient (which will be 100% of the time for us as we have many-DB's per server, and definitely enough space for a "Copy of one of them") I will do this. If all all hell breaks loose I like the idea of just being able to rename a couple of databases to get me to an "active" state, and not having the stress of a restore-from-scratch; also being able to use that copy-DB during the initial does-it-hold-water tests, after rollout but whilst still in limited-access mode for key-users, would answer any "I'm sure it didn't use to be like that/I can't remember if ..." questions.
No surprise, I assume?!!, that we have a naming convention of RESTORE_OriginalDatabaseName_yyyymmdd and that they get purged after a while, based on their yyyymmdd staleness. Our automatic add-new-DBNames-to-the-backup-job routine ignores any DB with a name that starts "RESTORE_"