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
Something for the Weekend – SQL Server Links 17/06/11
[…] T-SQL Tuesday #19 – Beyond Backups – Talking on this months theme it’s Robert L. Davis(Blog|Twitter). […]
T-SQL Tuesday #19 Wrapup | Allen Kinsel - SQL DBA
[…] Davis (B | T) writes about backing up system configurations in the case of a complete server failure. Good info in one place here about what you would loose […]