SQL is a dynamic and critical Windows Server application that requires planning to implement and protect. This article looks at the SQL backup and restore options available with BackupAssist.
Implementations of SQL can range from low usage in-house databases to very large databases that are in a constant state of change. The type of SQL implementation you have, will determine the type of SQL backup solutions you use.
SQL backup solutions
BackupAssist
A standard installation of BackupAssist can back up and restore a full SQL Server using the File Protection, File Archiving and System Protection backup types.
These backups:
- Can be scheduled to run at the most convenient time.
- Are VSS-Aware and create application-consistent backups of SQL servers.
- Can be used by the BackupAssist Restore console to restore the full SQL Server.
SQL Add-on
The BackupAssist SQL Add-on unlocks two powerful features, the SQL Server Protection backup and the SQL Restore tool.
These features support the following backup types and restore options:
- A daily backup of an SQL Server or selected databases. You can use this backup to restore selected databases or the full SQL Server.
- A transaction level backup of selected databases that runs throughout the day (at 5 minute or greater intervals). These backups can be used to perform point-in-time restores of an SQL database.
- Remote backups and restores so you can protect any number of remote SQL servers (on the same domain) in addition to the local SQL Server, using one Add-on license.
BackupAssist and SQL Add-on comparison
This table compares the SQL Server support provided by the SQL Add-on and BackupAssist’s standard backup types: System Protection, File Protection and File Archiving.
What SQL backup to use
Now that you know what SQL backup and restore options are available, let’s look at what solutions are best for you.
High usage SQL servers
If you have important SQL databases that are often changing, it’s not enough to have one backup at the end of each day. You will want to be able to restore databases as they were at different times during the day. These are called a point-in-time restores and require transaction level backups.
BackupAssist’s SQL Add-on can perform transaction level backups throughout the day using what is called a Transactional Schedule. To perform a restore, you just provide a time of day and the SQL Restore tool will display the transactional backup that was made closest to that point in time. You can then restore the selected database or databases.
A low usage SQL servers
If your databases do not change a lot, or the data is not important or dynamic enough to require transaction level backups, you will only need to perform a backup at the end of each day.
BackupAssist File Protection (replication) File Archiving (compression) and System Protection (imaging) can all be used to create VSS backups of an SQL Server, and then restore the entire server.
The BackupAssist SQL Add-on can also perform a daily backup of an SQL Server using what is called a Basic Schedule. This daily backup has the flexibility of being able to back up selected databases. These backups can be used to restore selected databases using the SQL Add-on’s Restore tool.
Remote SQL backups
For some SQL implementations, the ability to perform remote backups of SQL databases is an important consideration. For example, if you have an SQL database in a remote office or if you want to save on the cost of licensing by not having backup software on every server.
BackupAssist can perform remote backups of SQL servers using the SQL Add-on. The SQL Add-on allows you to perform both Transactional and daily backups on remote servers on the same domain. These backups are saved to the SQL Server that is running the SQL Add-on.
SQL Recovery
As with any server application, the ability to perform recovery is critical. A backup of an SQL Server (using either the SQL Add-on or a standard BackupAssist backup) can be used to restore all of the databases. However, if the physical server or the operating system is damaged and the computer cannot start itself, you need to be able to perform a recovery of the server that is running SQL.
A system recovery requires a bare-metal image backup of the server and a bootable recovery media. For this reason, you should consider scheduling an System Protection image backup of your SQL Server as part of any SQL backup plan.
- If you only have BackupAssist, this could be the daily backup that you perform.
- If you have the SQL Add-on, then this would be an additional backup. For example, you run a Transactional backup though the day and a System Protection backup each night.
Backing up SQL in Hyper-V environments
The SQL Add-on can be used to backup SQL Servers that are running in Hyper-V guests (VMs). The SQL Add-on should be installed on the guest VM that is running the SQL Server, not on the host. If your Hyper-V Server is running multiple SQL Servers on separate guests, it is possible to install BackupAssist on the Hyper-V host and use the SQL Add-on to back up each guest, but this is not recommended.
It is best practice not to run applications on the Hyper-V host because any problems or performance overheads could affect all of the guests, and software updates could require a server reboot, causing an outage for all of your guests and the services they provide.
Protect your SQL Servers with BackupAssist
BackupAssist provides the ability to backup and restore your SQL Servers. The SQL Add-on adds additional flexibility by allowing specific database backups and restores, near-continuous backups though the day and point-in-time restores. You can also use the SQL Add-on to back up multiple SQL Servers. This means flexible solutions for your SQL implementation and flexible pricing for your budget.
To learn more about how to protect your SQL servers with BackupAssist, see our:
Have a questions about SQL backups?
Leave a comment below, tweet us @BackupAssist or post to Facebook.
Sharing this article improves database security everywhere.
