T-SQL Tuesday #19 – Beyond Backups

T-SQL Tuesday #19 – Beyond Backups

This blog entry is participating in T-SQL Tuesday #19, hosted this month by Allen Kinsel (Blog|@sqlinsaneo). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Disasters & Recovery.

Any DBA worth his or her salt knows by now that they’d better have backups of their databases. I’m often surprised by just how many people aren’t backing up their system databases. I see a lot of people preparing for the loss of a database, but very few people preparing for the loss of the whole server. So what would you do if you lost a whole server? Restore user databases, and then what?

Part of the issue is that there are hundreds of DBAs espousing database backups, but very few talking about what else you need. don’t stop at database backups. You need to go beyond just backups.

These are some of the key things you could be losing if you do not back up your system databases and system configurations:

• Machine configurations
  • 32 bit vs 64 bit
  • Aliases
  • RAM/CPU/Storage requirements
  • /3gb switch
  • PAE setting
  • SQL components installed
  • Service accounts
  • Startup options
  • SSIS packages (file)
  • Local security policy settings
    • Lock pages in memory
    • Perform volume maintenance tasks
• master database
  • Linked servers
  • Logins
  • Endpoints
  • Server triggers
  • xp_cmdshell proxy
  • Credentials
  • Proxies
  • Operators
  • Alerts
  • Filestream settings
  • Port number
  • Event notifications
  • Server Audits
  • System configurations
    • Max replication text size
    • Max/Min Server Memory
    • Max degree of parallelism
    • Cost threshold for parallelism
• msdb database
  • SSIS packages (internal)
  • SQL jobs and schedules
  • Maintenance plans
  • Paths to and history of backups
  • Database Mail configuration
  • Data collectors
  • Utility Control Point settings
  • Log shipping settings
  • Resource governor configuration
  • Policy-based management settings
• distribution database
  • Replication settings
  • Replication subscribers and publishers
  • Replicated commands and transactions