By: Greg Robidoux | Last Updated: 2017-04-11 | Comments | Backup
Performing database backups seems like a trivial task for database administrators, but database backups are as important as ever and DBAs need to make sure they have valid backups in the event there is a need to recover part of the database or the entire database. Since this seems like such a routine task, things are sometimes overlooked and forgotten about until there is a need to do a restore.
To ensure you have a proper database backup strategy that works for your entire environment, you need to think about backups as a whole and not per database and this is where having a global backup and restore solution is key to having peace of mind and reducing the time needed to administer backups.
As mentioned, database backups still need to be created on a set schedule. This may be needed for recovery, testing or refreshing environments with the latest copy of a database. These tasks should be simple, but when you have several databases and instances to administer it's easy for things to get overlooked. This is where a tool like SQL Safe Backup (aka SQLsafe) can be a life-saver and time-saver for your SQL Server backups and it supports all versions of SQL Server from 2005 to 2016.
If you administer one database or several thousand, you are probably already familiar with the different backup options that SQL Server has to offer. I don't want to take time to cover backup basics, but would rather focus on the advantages of SQL Safe Backup over using the native options that come with SQL Server.
One great thing about SQL Safe Backup is that every native SQL Server backup and restore option is available. In addition, there are lots of additional features that just don't exist with the native backup and restore features that makes SQL Safe Backup an even better solution.
SQL Safe Backup offers a GUI interface, a web-based interface, wizards and command line access to administer all of the features it has to offer.
SQL Safe Backup Management Console
In the past, database backups were maintained instance by instance or database by database. Whether you used scripts and SQL Agent jobs or Maintenance Plans, you still had to connect to each instance to setup your backups, restore a database and to check whether there were issues.
SQL Safe Backup allows you to manage your entire SQL Server database environment from one central console, so you can think about overall backups as a fundamental part of your job vs. backing up individual databases on various SQL Server instances.
Below is the SQL Safe Backup Today view. This gives us an overview all backup and restore activity for today, so we can see successful backups and restores as well as any issues with backups or restores.
If we look at the SQL Server Instances, we can see all backup history as shown below. We can also sort by any of the columns.
We can also filter the data, so we can focus on status, events or even a specific date timeframe.
SQL Safe Backup Web Console
In addition to the desktop version of SQL Safe Backup, there is also a web version of the console, so you can access this data from anywhere. IDERA now offers a web-based console for most of their products, so you can remotely monitor and administer SQL Server. You can see below the interface looks a little different, but the features and options are the same.
SQL Safe Backup Policies
The heart of SQL Safe Backup is the ability to create policies for backups, restores and log shipping. SQL Safe Backup also allows users to manage native SQL Server backup file formats, so they can manage their entire backup policies environment through the SQL Safe Backup console. Policies allow you to think differently about managing your SQL Server backups and restores. Instead of thinking about backing up a specific database on a specific instance, policies let you think about backing up groups of databases that may exist throughout your environment. This allows you to think about the importance of your databases and making sure you have valid backups in place.
A policy can be as simple as backing up all of your user databases at 1am every night or further tuned to selectively group databases or instances together. Let's say we want to create a policy to backup all of the Sales databases on all of our SQL Server instances. SQL Safe Backup has a wizard that will help you setup the policy. Most of the options for creating backups are the same as when using SQL Server Management Studio, but the nice thing is that you can select multiple instances and also select multiple databases for each instance that should be part of this policy.
Using the backup policy wizard, you can see below I have two instances and selected databases from each instance as part of this policy.
Another nice feature of SQL Safe Backup is all of the different variables you can use to dynamically name your backup files. This allows you to dynamically name the backup files with variables for instance, database, type of backup, date, time and more, such as the following:
C:\Backup\%instance%_%database%_%backuptype%_%timestamp% (%ordinal% of %total%).safe
When setting up policies, you can also have SQL Safe Backup alert you to certain events that have occurred, such as successful or failed backups or restores. SQL Safe provides additional options that are not available in SSMS.
Here are the event notifications you can setup for backups.
Here are the event notifications you can setup for restores.
Log Shipping Notifications
Here are the event notifications you can setup for log shipping.
You can also create policies to automate your database restores. Let's say you have a test or QA environment that needs to be restored every night with the latest production database backup. You can create a policy that automates this, so there is no need to worry about whether you have the latest updates in these environments. The more you can automate, the more time you can focus on other DBA related items.
SQL Safe Backup also gives the ability to create more robust log shipping than what comes built into SQL Server. This is the wizard to setup log shipping.
You also have the ability to configure multiple secondary databases for the log shipping configuration.
Backup Compression and Encryption
Another feature of SQL Safe Backup is the ability to compress and also encrypt your database backups. Most of the data stored in databases is highly compressible, but native SQL Server backups do not compress the backup and therefore the backup size is almost as large as the database data files. Starting with SQL Server 2008, there is compression support with native backups, but there are not as many options as with SQL Safe Backup. With the compression options in SQL Safe Backup, you can compress your backups up to 90% which allows for a much smaller backup footprint on your network and also allows for faster creation and movement of these backup files.
In addition to compression, you can also encrypt your backups with AES128 or AES256 encryption. This ensures only authorized people can restore your databases.
SQL Safe Backup Virtual Database
A new feature in SQL Safe Backup 8.4 is the ability to create virtual databases from database backups. This is key if you don't have enough storage to do a complete restore of the database. Since the backup is already on disk an instant restore utilizes the actual backup file, there is not a need to create additional database files for the restore. This feature also lets you immediately access the data in the database backup file without having to wait for a restore to complete.
This is a great feature if you need to refresh just part of a database or query some historical data, by having quick access to the restored data you can fix the problem much faster.
Here is how this can be done using the web-based version. You can see there are only a couple of items you need to enter to have access to the database.
Once the virtual restore is ready, you can access the database from SQL Server Management Studio or via a query just like any other database.
SQL Safe Backup Instant Restore
This allows you to access a database much faster without having to wait for an entire database restore to complete. In some cases, a traditional restore could take hours for very large databases, but using SQL Safe Backup's InstantRestore you can access the tables in the database much quicker.
If you use the SQL Safe Backup restore wizard to do the restore, you can see the SQL Safe Backup InstantRestore option as shown below.
Command Line Interface
SQL Safe Backup now offers new command-line interface (CLI) scripting extensions. You can use these to create, update, and deploy new database policies, restore to point-in-time, and execute object-level recovery functions from the command-line interface. This lets you quickly make changes across the environment without the need to use the GUI to make the necessary adjustments.
As the cloud becomes more and more prevalent, there is a need to leverage what the cloud offers to make SQL Server management easier. One of the uses for the cloud is to store database backups. SQL Safe Backup now supports more cloud backup options such as the following:
- Supports Microsoft Azure Blob Storage as a location for backup and restore files
- Support for Amazon Simple Storage Service (S3) as an option for backup, restore, instant restore, and object level recovery (OLR). Users can initiate Amazon Simple Storage Service (S3) operations ad-hoc, and they can be selected from the backup and restore policies.
Additional Backup Features
There is also integrated support for EMC Data Domain (backup and restore) and for Tivoli Storage Manager striped backups to tape.
Since SQL Server backups are still an important part of the DBA role, why spend more time than is necessary to ensure you have a robust and complete backup / restore solution. SQL Safe Backup offers many features that enriches the native backup / restore options and provides a way to centrally manage your entire environment. Spend less time setting up and administering your backup solution and know you have working backups when needed.
Check out SQL Safe Backup and download a free 14 day fully functional trial from IDERA and see how this can create a better backup solution for SQL Server.
MSSQLTips.com Product Spotlight sponsored by IDERA makers of SQL Safe Backup.
Last Updated: 2017-04-11
About the author
View all my tips