{"id":43,"date":"2017-09-28T05:01:04","date_gmt":"2017-09-28T05:01:04","guid":{"rendered":"http:\/\/www.spktechfit.com\/?p=43"},"modified":"2017-09-28T05:13:41","modified_gmt":"2017-09-28T05:13:41","slug":"sql-server-database-backups","status":"publish","type":"post","link":"https:\/\/www.spktechfit.com\/?p=43","title":{"rendered":"SQL Server Database Backups"},"content":{"rendered":"

Backups are crucial. You need to have the right backups in place to have your data covered in the case of any disasters. In this post I go through the different backup types and some of the different strategies for implementing them.<\/p>\n

<\/p>\n

\"\"<\/h2>\n

Recovery Models<\/b><\/h2>\n

The first thing to decide on before the backup type, is the Recovery Model. You set the Recovery Model type for each database. The Recovery Model is what decides what data to keep in the transaction log and for how long<\/p>\n

    \n
  1. Simple<\/strong>:\u202f the transaction is written to the transaction log, but as soon as the transaction is complete and the data has been written to file, the space that was used in the transaction log is now reusable by new transactions. This\u202f means you wont be able to do point in time recovery, you can only restore to your last Full or Differential backup<\/li>\n
  2. Full<\/strong>:\u202f This is basically us telling SQL Server to leave the transaction log alone. It puts the responsibility on us to make sure the transaction log is emptied. It is emptied when you do a transaction log backup.If you aren’t backing up the transaction log it can fill up. This model allows you to do a point-in-time restore<\/li>\n
  3. Bulk Logged<\/strong>: this is for when you are doing bulk operations and you want to minimize the amount of logging during that operation. You cant do point in time recovery with this model as it doesn\u2019t have enough information recorded.<\/li>\n<\/ol>\n

    Backup Types<\/h2>\n
      \n
    1. Full Backups<\/strong> take a complete copy of the database and allow for a complete restore to a certain point in time. The good side of these is they backup everything, bad side is they take the longest.<\/li>\n
    2. Differential Backups<\/strong> allow to take a snapshot of all the data that has changed since the last full backup.\u202f These backups are cumulative, if you have multiple differential backups they will have all the changes that were included in the previous differential backups. EG: if you do a full backup on Sunday, then a differential backup each weekday, Friday will include all the changes that happened on Mon, Tue, Wed, Thurs. and Friday.<\/li>\n
    3. Transaction Log backups<\/strong>\u202f include data that has been modified since last full, differential or T-log backup. These are not cumulative because every time a transaction log backup occurs it truncates the log, which gets rid of all the inactive or old transactions\u202f that have been committed to the database. Transaction log backups give us point in time recovery.
      \nThe transaction log is a log of all the transactions that have occurred since the last truncation. Anytime a transaction modifies data it occurs first in the log, then when SQL server gets around to committing that modification to the database data, that entry in the transaction log is marked as inactive and the next time a truncation occurs it will get rid of that record.<\/i><\/li>\n<\/ol>\n

      Factors to consider when planning the backup strategy\u202f<\/b><\/h2>\n