* Backup Verification (Comprehensive)
- Verify your backups and test on a regular basis to ensure the overall process works as expected. What is meant by this is to:
o Contact your off site tape vendor to obtain a tape
o Validate that the tape goes to the correct office
o Validate that the vendor delivers the correct tape
o Validate that the vendor delivers the tape in the correct time period
o Validate that the software version you use to perform the restore is compatible with the version from the tape
o Validate that the tape does not have any restore errors
o Validate that sufficient storage is available to move the backup to the needed SQL Server
o Validate that the SQL Server versions are compatible to restore the database
o Validate that no error messages are generated during the restore process
o Validate that the database is accurately restored and the application will function properly
* Backup Verification (Simple)
- Verify your backups on a regular basis.
o Maintenance Tasks: Automating the RESTORE VERIFYONLY Process
o Verifying Backups with the RESTORE VERIFYONLY Statement
* Windows, SQL Server or Application Updates
- Check for service packs/patches that need to be installed on your SQL Server
from either a hardware, OS, DBMS or application perspective
* Capacity Planning
- Perform capacity planning to ensure you will have sufficient storage
for a specific period of time such as for 6, 12 or 18 months.
* Fragmentation
- Review the fragmentation for your databases
to determine if you particular indexes must be rebuilt based on analysis from a backup SQL Server.
* Maintenance
- Perform database maintenance on a weekly or monthly basis.
* Security
- Remove unneeded logins and users for individuals that have left the organization, had a change in position, etc.
* Shrink databases
- If databases or transaction logs are larger,
than necessary shrink those files to free up disk space.
Source:
http://www.mssqltips.com/sqlservertip/1240/sql-server-dba-checklist/