SQL Server Database backup method

Database backup is an important means to recover important data in time and prevent data loss in the case of data loss. A reasonable database backup scheme should be able to effectively recover important data when data is lost, and need to consider the difficulty of technology implementation and the efficient use of resources.

There are four types of database backups, which are applied to different situations and are briefly described below.

1 , full backup

This is the way most people use it to back up the entire database, including all database objects, such as user tables, system tables, indexes, views, and stored procedures. But it takes more time and space, so it's generally recommended to do a full backup in a week or January.

2 , transaction log Backups

The transaction log is a separate file that records changes to the database and only requires a small amount of time to replicate the changes that have been made to the database since the last backup. To make the database robust, it is recommended to back up the transaction log hourly or even more frequently.

3 , differential backup

Also called an incremental backup. It is another way to back up only part of the database, it does not use the transaction log, instead it uses a new image of the entire database. It is smaller than the initial full backup because it contains only the databases that have changed since the last full backup. It has the advantage of faster storage and recovery. It is recommended to make a differential backup every day.

4 , file, and filegroup backups

You can back up and restore individual files or filegroups in a database. This method of backup is usually less likely to be used and is often used for backup of important data. It requires that when the database is designed, it should be considered, the need to make a separate special backup of the table to be grouped, assigning them different filegroups (the table can only be placed on the filegroup, not on the specific file, a filegroup can be a file or multiple files), in order to do backup, the data can be backed up separately. This kind of backup processing technology is relatively high, not only to master the backup/restore method, but also to the database structure and database of the table data in a better grasp of the situation.

Usual Daily backup method

If you do a full backup at all, it is not always possible to be limited to the size of the full backup and the backup time. And only full backups, you cannot restore the database to a point in time. So, we need full backup + log backup. For example, a full backup every day, every 1 hours or a few minutes a log backup. When it comes to differential backup, because Microsoft's differential backup records the changes that have occurred since the last full backup, if the database changes very frequently, the differential backup will be close to the size of the full backup, so it will not be appropriate in this case. Therefore, the full backup + log backup scheme is suitable for the vast majority of users.

Do you have peace of mind after the backup file is transferred to the standby machine? Wrong. As DBAs also need to check whether backup files on the standby can restore the database to the latest, if the use of log backup, will not because the loss of a log backup file, the database can not be restored to the latest? How do I check that there is a suspended between log backup files?

The database in the backup, do not check the integrity of the data page, if the data page is broken, the backup job will still be executed, and will not error, until you find that the data page is wrong, you are probably already because of insufficient disk space, and deleted the early backup, The rest of the backup may contain corrupted data pages, and if the corrupted data page is a table header, you will never be able to recover the table.

SQL Server Database backup method