MSSQL Backup and Recovery

To establish a maintenance plan, you need to enable the SQL Server Agent service

Create three sub-jobs:

1. Weekly full backup, executed every Sunday zero

2. Daily differential backups performed at 12 o'clock Noon

3. Transaction log backups performed hourly, starting from 6:30 every hour, until 24 o'clock night.

If the request is higher, you can upgrade one more level:

1. Full backup every 0 o'clock

2. Operating hours, starting from 6:30 every day, each hour (30 minutes) differential backup, until 24 o'clock night

3. Working hours, transaction log backups are performed every 15 minutes. (45, 60,15)

Full backup can be restored at any time

Full backup + back differential backup for free recovery to point-in-time for differential backups

Full backup + Subsequent successive transaction log backups that can be restored to any point in time during the transaction log backup

(start) Full backup + (intermediate node) difference + (subsequent consecutive) transaction = minimum Recovery

-------------------------------------------------------------------------------differences or log recovery, you can find the front (rollback) point.

The full backup contains all the information, and the difference + transaction log is available.

A differential backup is all the difference from the current time at full backup, so the subsequent diff files will grow larger. Recovery, full backup (start) + differential backup (end point) = Restore to a specified point in time

Transaction log backups are the difference after a previous transaction log backup, so the log backups that follow are nearly large. On recovery, full backup (start) + continuous transaction log backup = Restore (rollback) to any point within the final point.

Email Notification:

1. SSMS Management Database Mail Right click Configure Database Mail, set up SMTP

2. SQL Server Agent Right-click Properties Alert System mail session, enable mail profile, select previously configured Database Mail

3. SQL Server Agent operator Right-click on new operator name + email address

4. SQL Server Agent job sub-job Right-click on the "Properties" notification "hook-and-Choose email" To configure the appropriate selection

MSSQL Backup and Recovery