Microsoft SQL Server is among the top database management systems in use today. It interacts with applications to enable various tasks, including transaction processing, business intelligence, and analytics. For many database administrators, backing up and managing SQL servers is one of the most important tasks in their job description.
Types of Backups
Microsoft SQL Server has three main types of backups:
Full: As its name suggests, a full backup includes all of your data—tables, indexes, functions, etc.
Differential: Backs up only what has changed since the last full backup. Differential backups are cumulative, meaning that each differential backup includes all the changes from the previous differential backups as well. For this reason, it’s recommended that you take periodic full backups to keep differential backup files small.
Transaction Log: Transaction logs keep track of every change to data and when the change was made. Backing up the transaction log in addition to the full and differential backups allows you to restore your SQL database to a certain point in time.
These backup methods work together. For example, you could take a full backup weekly, supported by nightly differential backups and hourly transaction log backups.
Common Mistakes in Managing Microsoft SQL
Backup errors can be costly and time-consuming. Here are a few common mistakes to avoid in SQL database backup.
Monitoring too much or too little. Check your SQL Server error logs regularly; ideally you would have an automated process for alerting you to errors. But be aware that too much information can also slow you down—don’t let your transaction logs get filled up with every successful backup. Restricting your transaction logs allows you to find critical information quickly.
Not having a clear plan. You need to understand your recovery needs, make sure you have the right strategy for different types of data, and define who is responsible for each part of the process. Having a documented plan will save you time and frustration during a recovery.
Not testing your plan. You don’t have a recovery strategy if it hasn’t been tested. When you actually restore your database from a backup before any data has been lost or corrupted, you can determine whether your plans include all the people and processes you need.
Not having a centralized backup manager. If you manage multiple servers, you will save time, effort, and money if you can manage all of your processes from one dashboard.
How Rubrik Can Help
Rubrik simplifies your database backups by managing all your SQL Server systems—whether on premises or in the Azure cloud—in one place.
Auto-discover all instances, databases, and clusters on each SQL Server.
Assign SLA policies to SQL Server and have all instances and databases inherit the policy. Different policies can be made on the server, instance, and database levels.
Use incremental-forever backups to reduce storage requirements. Unlike differential backups, incremental forever backups need only one full backup.
Reduce RTO to near-zero with Live Mount (see below).
Rubrik provides three main recovery options for different use cases.
Restore: Delete a damaged production database and recreate it with the same name and file structure from the Rubrik backups. This is typically used as a complete recovery when the original database is no longer functional.
Export: Create a new database from Rubrik backups without deleting or affecting the original. The new database can be renamed and reconfigured. This option is often used when a copy of the database is desired for testing purposes.
Live Mount: Mount database backup files directly on the Rubrik cluster. This can be done almost instantaneously because you don’t have to wait for the database to be copied across the network. Live Mount does not alter the original backup in any way, making this a quick and easy option for testing and item-level recovery.
Learn more about how Rubrik can simplify how you back up your Microsoft SQL Servers.
Frequently asked questions
How do I automatically backup a SQL database?
Rubrik can automatically backup all of your companies SQL databases into immutable cloud storage that is ready to be deployed at the first sign of trouble with a low recovery time objective.
Can you backup SQL databases while use?
SQL databases can be backed up while in use but it is important to ensure that your backups are capturing all information with a known RPO and RTO in case of problems.