Overview

What are Intronis’ best practices for backing up Microsoft SQL Data?

Feature

If you would like to backup Microsoft SQL databases, it is recommended that you create a specific job using our SQL plugin. Instead of selecting the SQL database folder location in a flat file backup, it's recommended that you create a separate job for it by going to the Backup Sets area in the software and selecting "SQL Server".

Our SQL plugin uses the same exact API calls that Microsoft makes when you click on a database in SQL and tell it to run a backup. We create .bak files of the databases in the temp folder chosen in the software and then back them up to our servers.

We allow you to backup both local and remote SQL servers using our software. It is best practice however to install the software directly on the SQL server whenever possible. If you would like to backup a remote SQL server, it is necessary to point our software to a temporary space directly on the remote SQL server, where we will create the SQL .bak file. You need to make sure that the user our BackupAgent service is running as has permission to write to that directory and that the user your SQL service is running as has permission to that temp directory as well.

Solution

What are the features and options when creating a SQL backup set?

When you go to create a SQL backup set in the software you will be able to search for SQL servers and instances that exist in your environment. You can then select the method with which you'd like to authenticate with that SQL server. You can choose either Windows authentication or SQL authentication.

Windows authentication uses the credentials of the BackupAgent service (we use the Local System account by default) to connect to the SQL instance. SQL authentication allows you to pick a user such as a SQL administrator account and plug in the credentials of that user.

You can then refresh your list of databases to view all of the databases on the instance you have selected to backup. You can select a single database or you can select multiple ones, de-selecting specific databases from the backup.

SQL backups are scheduled by default according to Microsoft best practice of having one full backup per week with six differential backups. Your first backup of the databases will be a true full copy, taking an entire copy. Day to day differentials would then just take the transactional changes since your most recent full backup, and the weekly full would use our Intelliblox technology to run a block level differential on the previous full backup.

We keep SQL backups on a weekly basis, by default keeping 4 weeks of SQL data. You can configure the software to keep as little as 1 week of data, or you can create custom rules similar to a tape scheme.

How can I best configure my SQL databases to be backed up by Intronis?

In order to successfully backup a database, its mode in SQL needs to be set to "Normal". SQL will not be able to backup databases that are in a "Shutdown" mode. To add or subtract databases from the backup set in our wizard the databases also need to be set to "Normal" mode. You can make the change to the database mode within SQL Management Studio.

In addition to having the database set to Normal mode, you should also make sure that the AutoClose option is disabled for each database you want to back up. AutoClose shuts down a database when the last user of it logs out, which in turn prevents our software from accessing the data to back up. For more information on AutoClose as well as how to disable it, please see this article from SQL Server Pro magazine:

SQL AutoClose Article

When databases are set to full recovery mode in SQL, this forces our software to take full backups instead of true differential ones. Our Intelliblox technology will allow block level differentials on previous backups, but we will have to copy all of the SQL transaction logs every time we do a backup, creating larger files. Full recovery mode specifies that all database transaction logs are saved so that you can restore to very specific transactions in the database history.

Our recommended configuration is to set your databases to simple recovery mode in SQL, which will allow us to backup smaller files and ensures that the transaction logs are truncated each time a checkpoint is issued for the database. This means that your daily differential backups will be much smaller. However, simple mode does not have the same level of granularity to restore specific points in database transaction history. To read more about this, please review this KB article on simple recovery model: