SQL Backup and Restore are the basic SQL components that provide essential safeguard to the critical data stored in the SQL Server database. A well planned backup and restore strategies would enable you to recover the SQL database from an unplanned disaster within a short timeframe.
Preliminary Backup Consideration
•The Backups created by more recent versions of SQL Server cannot be restored in earlier versions of SQL Server.
•A Full database backup take more time to complete and requires more storage space. For a large database, consider supplementing a Full database backup with a series of Differential database backups.
•Beside Full database backup and Differential database backup, Transaction Log backup can be taken in high frequencies to minimize the work loss exposure. In Production environment, a Full database backup should be taken at end-of-day during off-peak hours, where Differential database backup can take place in mid-day or hourly basis, and Transaction Log backup at a frequencies of every 15-30 minutes.
•Each successful database backup add an entry in the SQL Server error log and System Event log, this would resulting in huge error logs and create difficulties when filtering error messages during troubleshooting. You can suppress these backup log entries using trace flag 3226 if none of your scripts are depending on those entries.
Preliminary Restore Consideration
•Restoring SQL databases back to the SQL Server is a straight forward process. Complications arise when you attempt to restore SQL databases into a new SQL Server or entire SQL Server Applications due to the dependence with the Operating System.
•Restoring a database of 64-bit instance to a 32-bit instance is not supported.
•The Simple Recovery Mode does not allow for Transaction Log backup, therefore cannot be used for recover a point in time.
•The Full Recovery Mode it allow for Transaction Log backup and therefore it can recover a point in time. This mode prevent minimal data loss and it is the preferred recovery method for Production database.
•The Bulk-Logged Recovery Mode is best for recovery bulk-transactions in database. Full Recovery Mode can be switched to Bulk-Logged Recovery Mode during bulk-transactions and revert back during normal data recovery.
A SQL Database Backup and Restore strategies must be in place. The Database Administrator must ensure that the strategies are tested and enhanced in regular basis. All parties involved in the “SQL Database Backup and Restore” strategies must aware of their roles and responsibilities during the test in order to respond appropriately when the disaster strikes.
SQL Server database Backup and Restore is a compulsory “must-have” item in Database Administrator’s Task List, it is an inexpensive disaster recovery solution provided by Microsoft to safeguard the critical information in the organization.