SQL Server Backup Best Practices

          Best Practice recommendations for SQL Server Database Backups

       1.       All OLTP production databases should be set to use the full recovery model. This way, you can create transaction log backups on a periodic basis.

             2.       Whenever possible, perform a daily full backup of all system and user databases .

               

                     (Note : Recommended as per your infrastructure standards for the project )

             3.       For all OLTP production databases, perform regular transaction log backups, at least once an hour.

             4.       Perform full backups during periods of low user activity in order to minimize the impact of backups on users.

             5.       Periodically perform test backup restores to ensure that your backups are good and can be restored.

             6.       Encrypt your backups in case they should become “lost.”

             7.       Store backups offsite and in a secure location.

             8.       If using SQL Server encryption, be sure to back up the appropriate service master keys,database master keys, and certificates.

             9.       If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much space on your storage device, consider a third-party backup program, such as Red Gate SQL Backup Pro and HP Data Protector etc.

             10.   Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You don’t want to be looking this information up during an emergency and also it would be useful for upcoming DBA's in your environment.