To keep your Microsoft SQL Server databases safe, you need to back them up. Today, it is considered a best practice to have at least two backup copies of all of your data, one on-site and one off-site.
In this article, we will demonstrate how MSP360 Backup can provide both on-site and off-site SQL database backups by backing up data to both local storage and cloud storage, and how to backup a database in SQL Server.
Table of Contents
SQL Database Backup and Restore with MSP360 Backup
MSP360 Backup integrates with Microsoft SQL Server’s built-in backup and restore mechanism. MSP360 Backup also features encryption, flexible scheduling and a range of other options.
You need MSP360 Backup for MS SQL Server or MSP360 Backup Ultimate editions in order to be able to perform SQL Server database backup and restore.
In this article, we’ll describe how to back up a database in SQL Server using MSP360 Backup. We’ll also explain some important details that you need to know and take into account when performing backups of your databases.
For instructions on the SQL Server database restore process, please check out our article “SQL Server Database Restore.”
For the purposes of this article, we assume that you’re already familiar with how SQL Server functions. We’ll focus below on how our backup solution interacts with the SQL Server.
How to Back Up a Database in Microsoft SQL Server
Launch the Backup Wizard by clicking MS SQL Server button on the main toolbar or pressing Ctrl+B.
In the Backup Wizard, specify whether you want to perform backup to a single location (Local or Cloud Backup) or whether you'd like to back up to a local storage first and then to a cloud storage (Hybrid Backup).
In this guide, we will choose the Hybrid Backup option, which allows us to follow the 3-2-1 backup rule.
Select a local storage for your backup and click Next.
Select a cloud storage location for your backup and click Next.
Enter a plan name and proceed to the next step.
Select the desired SQL Server instance from the drop-down menu and enter your login authentication type/credentials. Enable the option Check if the specified account has necessary permissions to perform backup in order to make the wizard automatically check whether the specified user account has all necessary permissions for backing up the database. When you’re done, click Next.
On the database selection step you have 3 options:
- Back up all databases.
- Back up all user databases only.
- Back up selected databases.
Select the option you desire and click Next.
Configure compression and encryption options.
Microsoft uses a proprietary backup compression algorithm to compress data for SQL Server in certain versions. Here are the versions that support this feature:
- SQL Server 2008: Enterprise, Developer.
- SQL Server 2012 and higher: Enterprise, Business Intelligence, Standard, Developer.
MSP360 Backup will use native SQL Server compression if supported by your SQL Server version. Otherwise, MSP360 software will use a custom compression algorithm.
As for security, you can choose whether you’d like to use 256-bit encryption offered by MSP360 Backup.
Specify the retention policy.
You can save only the necessary number of copies. This helps to prevent you from running out of space or paying more than necessary when using cloud storage.
On the scheduling step you have the following options to choose from:
- No schedule or Specific date. If you choose this option, your backup will run as a one-time job; in other words, a full-backup will be performed with no differential backup or transaction log backup taking place.
- Recurring (predefined templates) or Recurring (advanced schedule). These two options enable you to configure the frequency of full, differential, and transaction log backups.
Here are the definitions of these SQL database backup types:
Full backup — a database backup that contains all the needed information in a database to perform a full restore.
Differential SQL Server backup — a database backup that contains only the data that has changed since the last full backup.
Transaction log (T-log) backup — a backup of current database transactions that includes all log records that were not backed up in a previous log backup.
Note that SQL Server database backup and restore operations occur within the context of the recovery model of the database. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. If you want to perform transaction log backup, ensure that your database recovery model property is set to Full or Bulk; any other value will result in an error.
Having selected the Recurring (predefined templates) option on the previous step, you’ll be able to choose from one of our recommended templates.
Alternatively, if you use the Recurring (advanced schedule) option, you can set the required frequency by minutes/hours/days, schedule bi-weekly backups, and so on.
Set up pre- & post- actions: specify scripts to be executed prior to and right after the backup plan execution. You can also create a backup chain.
Complete the backup wizard and wait for the next scheduled execution or, if you wish, execute the backup plan immediately.
Once complete, the backups will be displayed in the backup storage tab.
How to Backup a Database in SQL Server Using More Than One Product
We don't recommend using multiple backup methods for a single SQL Server. You should instead use a single backup software or scripts in order to keep your toolset and process simple, and avoid data conflicts.
If you want to perform SQL database backup to two different locations, take advantage of the Hybrid Backup feature offered by MSP360 Backup. (This option was chosen in step 2 of our guide above.)
If you have two products running backups on the same set of databases, you may end up with differential and transaction log backups that cannot be restored.
As an example, let’s say you use two backup products and do the following procedure:
- Back up your customer database using MSP360 Backup (CustomerFull1).
- Perform the first differential backup for that database using MSP360 Backup (CustomerDiff1).
- Back up the Customer database using another product (CustomerFull2).
- Perform the second differential backup for the master database using MSP360 Backup (CustomerDiff2).
You would expect that CustomerDiff2 could be restored after CustomerFull1, but in reality, CustomerDiff2 is based on the CustomerFull2 database, which was created by a different product. You may not be able to perform the restore in the way you expect.
This case only applies to individual databases. If you perform a backup of two different databases using two different backup plans or backup products, there should be no issues.
- Full, differential and transaction log backup
- On-site and off-site backup options
- Image-based and system state backup
