Microsoft SQL Server databases are often at the core of many organizations’ IT infrastructure. If that describes you, a rock-solid backup and recovery environment is a must. You do NOT want to be caught flat-footed when disaster strikes your SQL Server data. What follows is my best attempt to impart some hard-won backup wisdom on how to properly safeguard those databases.
The #1 rule when it comes to SQL Server backups is: Have backups and TEST them regularly! Even the most meticulously orchestrated backup plan is useless if you cannot reliably restore databases when needed. But before we can restore, we must back up, so let’s discuss the core backup types and how to employ them.
SQL Server Backup Modes
There are three backup modes that every DBA must know in order to effectively back up SQL Server.
Full Database Backups
As its name suggests, this backup copies all data within the database. Think of it as a complete picture of your data at that point in time. Perform these weekly or monthly.
Differential Backups
This only backs up data that has changed since the last full backup. Much faster and smaller in size compared to full backups. These should be performed daily.
Transaction Log Backups
These are copies all transaction activity since the last transaction log backup. Create these types of backups every 30 minutes to minimize potential data loss. If you do not do this, you will only be able to restore your database to the last good backup (full or differential). Also, the transaction log will fill up and cause problems with the database.
How Often Should You Do a Backup?
Combine these three backup types with short backup intervals. This will give you solid protection against data loss while also guarding against storage needs and backup times ballooning out of control. Choosing how often to do a full backup is really up to you. Take a look at how large your differential backups are getting when you go more than a week or two between full backups. If they don’t grow too large, you can save a lot of time and storage by doing monthly full backups instead of weekly ones. But if they are approaching the size of a full backup, you are better off doing more frequent full backups.
Choose Your Backup Destination Wisely
Storing your backup files on the same volume as your SQL Server database files violates every best practice and drastically hinders recoverability when drive failures strike. Remember the 3-2-1 rule, where you have three copies of all data (including the original), place two on storage with different risk factors, and store on off-site. For the different risk factors, you can use different kinds of disk, disk and cloud storage, disk and tape, etc. Each of these has different risk factors, or things that might damage them. Finally, one copy needs to be stored off-site, with just one minor addition - immutability. (Immutability used to be assumed due to tape; now it cannot be assumed.) Since backups are increasingly stored on disk, and disk backups can be deleted by a hacker, we need to add the requirement that at least one copy needs to be stored on immutable storage. The easiest way to do this is to use object storage with object lock. You will most likely need some type of third-party replication to replicate a copy of your backups into object storage and set its retention lock. That way, hackers cannot delete your backups even if they control your environment.
Logistics
You want to make sure that your SQL Server backups are included with the rest of your backups, so that it all gets treated and handled the same. There are four ways to do this, each with its own unique advantages and disadvantages.
Dump & Sweep
The most common method of including your SQL backups with the rest of your backups is to back them up to some type of storage that then gets backed up by your file-system backup tool. We call this dump and sweep. This leaves you a local copy for fast restores, but also requires a lot more storage to hold that copy. Another concern is that you need strong reporting and error handling on the dump stage, to ensure that it continues to happen.
Dump & Replicate
This is similar to dump & sweep, but it has you backing up SQL to some type of deduplication appliance that then replicates that backup to another appliance. This is probably the most storage-efficient approach, but such appliances can be costly. You also need the error handling and reporting mentioned in the previous method.
Agent or VSS
This would be my preferred method, which is to use the agent your backup software product offers for SQL Server or tell your backup software to use Windows’ VSS features to give it a point-in-time copy of the database to back up. This is the most elegant method, as it integrates the error handling and reporting of your backup software into your SQL Server backups. However, many DBAs do not like it because they do not control SQL Server’s backups.
Put Your Backups to the Test
If you aren’t routinely testing restores of your SQL Server backups, then you may as well not have backups at all. Yes, you read that correctly. Backup testing needs to occur at least monthly, if not more frequently.
Start by restoring your most recent full backup onto a test environment, such as a VM in the cloud. Verify database consistency via a DBCC CHECKDB. If errors appear, you have bigger issues to contend with. Then play back additional differential and/or transaction log backups to simulate point-in-time recovery. How smoothly and quickly this process goes dictates how well your protection plan will work when crunch time arrives.
Repairing a Database with a Backup
I sincerely hope these pointers on how to properly backup your SQL Server environment were helpful. Unless you enjoy working weekends and all-nighters to manually rebuild crashed databases, I urge you to start rigorously backing up your SQL Server data, and testing how well those backups work. Don’t become another painful statistic or cautionary tale.
If in the event you do not have a backup, you can use a third party tool like SecureRecovery® File Repair Software for corrupted Microsoft® SQL Server Databases. This tool helps rebuild databases for many different SQL server types.
.png){kind=small}
