SQL Server creates a complete data backup system

Let's look back at the first half of the data-related events in the IT field and re-deepen our understanding of the importance of data backup:

January 2017 MongoDB Hacker Ransom incident, found some in the Internet users of MongoDB no protection measures, the attackers have deleted the database, and left a database called WARNING. "I have transferred your database in MongoDB, if you want your data, give me 0.2 bitcoins (about USD200). ”

February 2017 Gitlab An exhausted system administrator who worked late in the Netherlands and accidentally deleted a directory on a server that should not be deleted during a frustrating database copy: He completely deleted a folder containing 300GB of active production data. Even more tragic is that scheduled backups and cloud backups are not valid.

April 2017 Cloud hosting service provider Digital Ocean repeated the Gitlab 2 months ago that a production-level database was deleted due to carelessness, resulting in a five-hour failure.

In June 2017 , a cloud host in The Hague, the Netherlands, Verelox.com, a former administrator erased all of the company's customers ' data and wiped out most of the server's content, and the customer data recovery was hopeless.

For these frequently occurring events, we have a deeper understanding of the first of the DBA code: Effective backups are more than everything.

There is an urgent need to improve and refine the backup effort, and the DBA optimizes the backup as follows:

1. Backup removal optimization
The previous deletion was based on the time fits, deleting all Files 30 days ago.
Now, based on the principle of SQL Server Backup recovery, the database restore is based on completeness, and we've adjusted the delete policy to delete all previous files that were last completed 30 days ago.

2. Backup Continuity Verification
Reads the last complete and subsequent log backups before the current time to determine the continuity of the LSN.

3. Backup Availability Verification
Rotation all backup files for nearly a day, RESTORE Verifyonly verifies the availability of the backup files.

4. Backup Recovery Verification
Given the lack of resources, a library is validated one at a time, the database is verified by the queue, the most recent complete and subsequent log backups are read before the current time, and a backup recovery operation is done.

Recover verified data The remote UNC path is mounted at the far end, and after verifying a library, clean it up immediately to avoid space consumption.

5. Backup history and job history automatic cleanup
Different granularity can be configured to periodically clean up the backup history and job history data stored in msdb, reducing the IO bottleneck in msdb.

Based on the above 5 points, to create a complete data backup system, for the effective use of data escort.

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1948417

SQL Server creates a complete data backup system