Organizations need to make sure their backup process for SQL Server databases are as through as possible. In this article we offer you a checklist that will go a long way in making your backups effective.
We cannot stress enough on the importance of backups in SQL Server. For the backup process, it is not only important to do them, but also to do them right, to store them right, and to use them right. Backups in SQL Server might seem like just another important, regular process, but it is also something that is complicated and requires proper understanding before being carried away. You cannot and should not be performing backups without understanding their importance and the right way to do them. A wrongly done backup is more harmful than no backup at all. Given below are the important points of your backup checklist, make sure you understand them and follow them before you back up data.
- Frequency – You need to be very clear with the fact that how regular are the backups supposed to be. You cannot be opting for weekly backups if the company cannot even afford to lose a day’s work. Make sure you understand the needs of your bosses well, and back up accordingly. It is advisable to regularly go for full backups followed by log backups and differential backups.
- Validate Backups – It is not only important to successfully end and store your backups, you should also be able to restore them. Practicing restore commands will help you in the long run. At the time of disaster, you might panic seeing an error message, however if you have already dealt with it before, it will be easier to deal with it again.
- Safe Storage – You might choose to store backups on local drive, but you should also be regularly moving data from that local drive. There is a possibility that you might lose data from the drive also, if anything happens to it. It is only safe to copy data from drive. You should also have a clear record of how many copies do you have, of your database, where are they located and how safe are they.
- Retaining Data – A lot of the time users tend to delete data that gets old, along with deleting backups. Make sure you adhere to the retention policy of the company and do not delete anything before the right time. It is also important to regularly run the DBCC CHECKDB, and keep checking your databases and backups for corruption. Use compression to make your backups occupy less space.
- Restoring Backups – After you are done storing backups in a safe location, and retaining them for the necessary period of time, it is now time to restore all your Full, Differential and Log Backups. This is a tricky process and it is suggested to make use of third party software for doing this. You might choose to make use of maintenance plans for this, but that will only make things more difficult, you might end up losing msdb history, if the server used for backup is not available at the time of restoring.
Despite all the best practices you follow including this checklist, incidents of SQL corruption can at times prove beyond your established backup process. In such scenarios immediately call in a specialized sql recovery tool.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged access and sql recovery software products. For more information visit www.datanumen.com