In this article we look at choosing the apt backup option depending on our specific needs.
Backing up data in SQL Server is an important and a challenging exercise. Like many other SQL Server processes, backup also involves a lot of factors that should be kept in mind. While backing up your data it is not only important to ensure the preservation of your data, it is also necessary for maintaining business continuity. There are different types of backups that you can use to back up your SQL Server data, and not choosing the right backup option might lead to complications in future.
While making a selection for the ideal backup type, you first need to understand what the different types of backup options are and what the ideal situations to apply those backups are. Backups are classified in two categories: Simple Recovery Backups and Full Recovery Backups. As the name suggests, simple recovery backup is the one in which some amount of data is lost. Whereas, in full recovery backup, all the data is restored and there is no data loss. Given below is a description of different types of backups under these two broad categories, along with the best suited scenarios for these backups.
SIMPLE RECOVERY BACKUPS
As stated above, Simple Recovery Backups are used when you can afford to lose some data. So if you are OK with losing a day’s progress or more/ or less, then this can offer you quick and reliable backup options. All you need to do is; in the properties of your database, select ‘Simple Recovery Model’. Under this model, you have the option of applying two different backups.
- Full Backup – This is usually the first choice of many users, but this should ideally be used for short and very important databases. This is the simplest and quickest of all backup options. Using this you can back up the most important of information with little loss. However one of the greatest drawbacks of using this option is that it takes up a lot of space.
- Full + Differential Backups – This is done to backup the overhead changes made in a database after a full backup. Meaning, if you attempt a full backup and then make some changes, and do a differential backup, followed by another differential backup, after few more changes, the first differential backup becomes unnecessary and can be removed.
FULL RECOVERY BACKUPS
If you want to back up data in a way that nothing is lost, select the ‘Full Recovery Model’ in database properties. This will give you backup options with no data loss.
- Full + Transactional Backups – By making use of Transaction logs you can restore complete data, but first you need to do a full backup followed by a series of Transaction log backups.
- Full + Differential + Transaction Log Backup – To reduce performance overhead on server, you can replace full backups from previous scenario to differential backups. And then restore by starting from full, to differential, followed by the remaining transaction log backups.
The SQL data file can get corrupted during the backup process
During the backup process, the SQL data file you backup may end up getting corrupted. At times this corruption is only discovered when you are attempting to restore your database using the stored copy. In case you encounter such an issue, you should immediately call in a sql recovery tool like DataNumen SQL Recovery to get back the data from the corrupted file. Designed to extract every possible data element stored in the SQL file this tool works like magic for negotiating logical errors. Moreover the whole recovery process can be completed within a short span of time with this tool.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com