I was happy to see that we do quite a few of the things on Brent Ozar’s Backup best practices page.

http://www.brentozar.com/archive/2009/03/dev-test-and-production-sql-server-environments/

I’ve one-upped Brent on one point. We have an SSIS package which restores databases from server to server using our DBA_REP databases which I borrowed from Rodney Landrum and modified for our environment.

One of Brent’s ideas I appreciate is his approach to keeping management apprised on restore times:

Keep management informed on restore time estimates.

Every quarter, I look at the backup sizes & speeds, and estimate how long it will take to restore a server from tape. I turn that into a written report to management, and for each server, I give cost estimates to decrease that outage window. Sometimes the answer is third-party backup compression software, sometimes it’s more CPU power for the server, sometimes it’s adding memory, etc. The primary goal is to give them enough information to make a good business decision about whether it’s worth the money. The secondary goal is to CYA: when a server is down, and a restore is taking longer than the business owners would like, then it’s a great time to pull out last quarter’s report and say, “See, I told you this would take an hour to restore, and you said it wasn’t worth the $5,000 to shrink down. Should we reconsider that decision now?”

I think we’ll be using this approach on a new project.