carloscastilla - Fotolia
Looking for something else?
Microsoft provides out-of-the-box backup capabilities for SQL Server via the Backup command. Although these native SQL Server data backups work, they don't scale well. Database growth or an increase in the number of databases or SQL Server instances makes the native backup capabilities ineffective. So, a third-party backup solution should be considered essential for a production SQL Server deployment.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
Scalability isn't the only challenge that comes into play. It's important to consider the amount of time that it takes to perform a recovery operation.
Some sites recommend making a full SQL Server data backup on a weekly basis and then backing up transition logs every few hours. This approach works, but you have to consider the volume of data that could potentially be lost in a failure (everything that has been written to the database since the most recent successful transaction log backup) and the length of time that will be required for a restoration.
Depending on the circumstances, a restore operation could require you to recover a lot of transaction log backups, which is tedious, time-consuming and potentially error-prone. You can greatly reduce the number of transaction log backups to be restored by performing some periodic differential backups.
Differential backups should be run on at least a daily basis, but in some cases it is more effective to run differential backups every few hours. In the event that a recovery operation is required, you would need to restore the full backup, the most recent differential backup, and any remaining transaction log backups. The end result should be that you have to restore far fewer backups than you would if you were relying solely on a full backup and a bunch of transaction log backups.
Best practices for SQL Server backup, recovery
Top tips for SQL Server backup and recovery
Dig Deeper on Backup and recovery software
Related Q&A from Brien Posey
IT can have trouble managing user profiles that are separate from the desktop images in nonpersistent VDI, but they can make things easier with user ...continue reading
Microsoft Excel may be widely used, but other spreadsheet applications such as Google Sheets and Thinkfree are available for organizations to use.continue reading
Social network backup tools are hard to find, but they are out there. Like with file data, your organization should make sure its social media data ...continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.