In my previous posts, I explained the importance of the transaction log in SQL Server and essential concepts you should understand if you are managing SQL backups. In this final post in the series I’ll provide some essential SQL back-up and management tips.
Backup Scheduling
At Fenwick, we use Ola Hallengen’s freely available scripts for managing the creation and cleanup of backups. These scripts are easy to use and very reliable. Instructions to get started can be found here.
A typical schedule for a large database might be:
- Weekly full back ups
- Daily differential backups
- Transaction log backups every 15 minutes
Differential backups are not mandatory. If you have adequate disk space and your database is not so large as to make full backups very time consuming, then you could use a schedule like this:
- Daily full backup
- Transaction log backups every 15 minutes
Disk Redundancy and Independence
If you are the administrator of an on-premises environment, you need to be ready for disk failures. Assuming you are using a RAID configuration for redundancy, you should have spare disks close at hand. It is not unheard of for a second disk to fail while waiting for a replacement to arrive.
It is good practice to separate the storage of your backups from the storage of active databases. This way you will not lose everything if an entire array fails.
Off-site Backups
It is important to replicate backups to an off-site location. This provides more levels of independent redundancy: the data is on an isolated network and disk system, and in a separate geographic location.
Fenwick can provide geo-redundant, off-site backups of your on-premise SQL database through the Fenwick Cloud Platform.
Databases in ‘Recovery Mode’
If SQL server does not gracefully shutdown, due to a power outage for example, then SQL will automatically recover each database to its latest consistent point by replaying the log file. This can take some time. While this is happening the database will be marked as ‘Recovering…’ in SQL Management Studio, and connections will be refused.
A database in Recovery Mode will normally become available again eventually, and is not typically anything to worry about, unless the shutdown of the server indicates some other instability in your system.
The progress of the recovery, including an estimated finishing time, is visible in the server logs.
“My Transaction Log is Huge!”
The most common SQL issue reported to Fenwick is that the transaction log is huge and filling up the disk drive. This problem will occur if you have not scheduled transaction log backups, as SQL does not allow transaction log entries to be cleared away until they have been backed up.
To rectify the problem, take a transaction log backup immediately, and then use the Shrink function on the database through SQL Management Studio, which will reclaim the disk space.
The last three posts have provided essential information and tips for anyone who wants to understand the basics of SQL database management; Back-up; Recovery; and tips to help manage common problems.
