Aug 16

SQL Server : Backups #38

Why are backups needed?

To protect accidental data loss. Still, if you use highly available and fault-tolerant systems like SAN, and RAID, you may need to backup your database regularly. These fault tolerant systems can not provide accidental data delete by users, accidental data corruption from software and hardware failure. If you have a disaster recovery site or a secondary datacenter to where all data are replicated and data changes are stored, then you may not need backup but otherwise regular backups are always useful.

Data backup can restore data until the last backup. . Transaction Logs may help to restore data up to the point of failure. Restore data from the data backup, then if you have the transaction log up to the failure apply it to the data.

SQL Server Recovery Models:

  • Simple Recovery Model. Transaction logs are not used
  • Full Recovery Model: Use both data and transaction log backups
  • Bulk-logged recovery model: Like full recovery model. But here bulk operations are minimally logged to the transaction log. So full recovery becomes faster.

Types of Backups

  • Full database backup: Full database, all files and file groups are backed up
  • Partial Back up: It’s not the differential backup. Here, all primary file groups and any other file groups that are read-write are backed up. But read-only file groups are not backed up by default.
  • File or file group backup: Usefull when full back ups take too long. Backup in parts.
  • Differential back up: Backup only the changes. Should be applied to a full backup.
  • Log backup: In log back ups, transaction logs are backed up regularly. It helps in bulk logged recovery model and full recovery model.
  • Copy only backups:
  • Full text catalog backups

Knowing about backup devices, media sets, and backup history tables is also important.

Mirrored Media Sets: Backup data to more than one media at the same time.

Backup Strategy:

Based on your company need and type of data, you should come up with a backup strategy.

Some backup strategies

Strategy – 1

  1. Take full database backup every saturday night
  2. Take a differential backup every wednesday night
  3. Perform, continual transaction log backups every 30 minutes

Strategy – 2: If the database is too big, and user activities need continuous uptime and optimal performance

  1. Filegroup 1 backup every saturday night
  2. Filegroup 2 backup every sunday night
  3. File group 1 differential backup every Tuesday night
  4. File group 2 differential backup every wednesday night
  5. File group 1 differential backup every Thursday night
  6. File group 2 differential backup every Friday night
  7. Perform, continual transaction log backups every 30 minutes

Backup System Databases

It is also important to back up system databases like master, model, msdb, tempdb, distribution, resource.

From: http://sitestree.com/?p=4837
Categories:38
Tags:
Post Data:2011-02-01 01:27:56

    Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
    (Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a> 
    In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
    <a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
    8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
    Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
    Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>