All about SQL server and automaticallybackup database

Posted by julesalanna on December 22nd, 2017

There are chances of occurrence of catastrophes, leading to loss of important data stored in the SQL server database. To prevent this, one needs to back up the alterations made to the data on a regular basis.

Proper backups reduce data loss and increase the availability of data. There can be various types of crashes apart from natural crashes like media failure, the disk getting dropped from a table or hand, hardware failure or an irrecoverable loss of a server. Backups are also necessary when the database has to be copied from one server to another, set up of Always On availability groups, mirroring of the database and archiving.

It is always recommended to place the database and its backup on separate devices because if at all the device where the database resides crashes, the backup will also be lost. Besides this, when the database and backup are kept in different devices, the I/O performance gets enhanced for writing the backups as well as the use of the database for production.

When there is less number of databases, they can be easily backed up routinely with least effort by using simple scripts but when there are a large number of databases which needs to be backed up, doing it manually becomes cumbersome as it would take a lot of time. Here comes the need to fix the problem by creating a way to back up all the databases or the selected databases automatically in a routine manner. It is also needed that the solution should not have any negative impact on the performance resulting in downtime.

It was usually thought that certain objects in the database get locked while performing the backup operation which leads to denial of the access to the database. But the fact is that, though the database backup utilizes some of the resources of the system, it is carried out in online mode in the SQL server which doesn't lock the user objects. The database backup operation needs disk reads as well as disk writes which needs to be configured accurately or else it can cause certain pressure on the I/O subsystem leading to timeouts for certain user transactions. Taking an instance, when the database is backed up to the same disk where the data files are present, can lead to timeout errors, most probably when the disk drives have a lower number of spindles. In such case, following operation can be performed by the system:

• Read-write operations to the database files for all the transactions.
• Reading the database file for backup.
• Writing the read data for the backup file to the same disk.

In such cases, there is a high load on the I/O system on the servers where the traffic is very high, which sometimes results in timeout error for certain transactions. In order to avoid this error, the backup must be scheduled at times when there is less traffic on the server.