Skip to main content

How to Backup SQL server like Batman: The Ultimate SQL DBA Guide


Let's get it straight. You want to take care of your SQL DB like a pro. But where to begin? Properly understanding and answering this question is very important to guide your way to a perfect DBA.

First, we need to understand what are the basic needs of our Database? For answering this question, we need to think SQL Server like ourselves. Maslow has defined a hierarchy of human needs. Likewise, we need to define the hierarchy of our SQL DB needs.

The very first thing your SQL Database needs is itself. So, you need to take care of this first before thinking about anything else. We call these Backups. You need to take backups of your SQL server on a regular basis so that when you hit a disaster, you can start from the last backup. Before ensuring this, you cannot and should not move on to the next need of your database. It’s like a human’s basic physiological need – food, shelter, clothe, education, and medication.

So, how you can ensure the proper backup of your database? SQL Server has a nice GUI for that you can use it in SSMS. You might want to schedule your backup in the SQL Server maintenance plan. Let’s say, you want to schedule a full database backup every week. So, we should create a maintenance plan in SSMS which will take a backup every week. You probably think taking backup every week will consume a lot of your space if you have a big size database. There’s a work-around for that, in the maintenance plan, you can specify when this backup set will expire. If your backup plan if for one week, you can set this expire time to 14 days. So, every week there will be new backup taken and the old backup which is 2 weeks old will be expired. So, your space issue is lessened.

Now how about if you need the database backup in the middle of the week? If you take a backup every Friday night, and you hit a disaster on Tuesday, you can restore the database on Friday. But what about the transactions occurred between Saturday and Tuesday? Your business stakeholders might not accept that. So, what can you do about it? You have 2 options here. Taking the backup more frequently is one of them which I do not suggest. Why? Because taking backup is expensive and if you choose to backup in between working days, the database might be slow and the end-users won’t be happy about it. So, we jump to option 2, which we call taking differential backups. Wait a minute. What is a differential backup and how can we perform this?

Differential backups are incremental backups from your last taken full backup. Let’s say, you have taken a 10GB full backup on Friday. On Saturday night, the database grew and now your DB size is 11 GB. If you take a differential backup on Saturday night, it will make the changes that occurred on Saturday and take the new 1 GB backup. If you schedule the backup on everyday end until the next Friday. We will get one differential 1 GB backup every night. So, if your DB goes down on Tuesday, you no have one full backup of last Friday and 3 differential backups on Saturday, Sunday, and Monday. You can restore these full backups and 3 differential backups and go back to the state of Monday. So, you lose one day worth of data. What if your management is not satisfied with this as well? Here come transactional log backups. Wait again, what is transactional log backups and how can we perform this?

Differential backup

Transactional log backups are log backups after the differential backups. Let’s say we take log backups after 1 hour. So, on Tuesday at 12 PM, we have 12 transactional log backups from 12AM to 12PM on Tuesday. Let’s say, our DB goes down at 10:30 AM, So now we have 1 full backup, 3 differential backups and 10 transactional log backups with which you can restore back your data to Tuesday 10AM state. So, you lose 30 minutes’ worth of data. What if your management is not satisfied with this loss as well? Pretty straightforward, we just need to take transactional log backups more frequently, like after each 15 minutes or 10 minutes. It all depends on your business rule.

Hope it helps! Next, we will discuss the second hierarchy need of your database. Stay tuned. Can you guess what the second important need for DB after Backup?

Labels:
Location: Dhaka, Bangladesh

Comments

Post a Comment

Most Loved Posts

SQL Insider 01 : An Anatomy of SELECT

Introduction When we write queries, we tend to think about the internals very little. In the new series of SQL Insider, I shall try to demonstrate what your SQL Server has to go through when you write a specific query, more specifically a specific operator. In the series, we shall try to cover all the important operators in SQL. Our today's SQL participant in SELECT. SELECT  With the SELECT query, we can select one, some, or all the columns of a SQL table. The typical syntax for SELECT is like this  SELECT * FROM Sales.SalesOrderDetail SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail sod Please note that we will not be dealing with WHERE clause in today's episode.  Database We will be using AdventureWorks2019 Database for the demonstration Important Configuration We will be setting STATISTICS IO ON like this - SET STATISTICS IO ON; SET STATISTICS IO ON ; We will turn on Actual Execution Plan to examine the query SQL Insider Let's sta...
Post a Comment
Read more

Intelligent Query Processing in SQL Server 2019 Big Data

SQL Server 2019: Intelligent Query Processing SQL Server 2019 ships with some brand-new features. Many of these features are targeted for Big Data Solutions. No wonder in that, since the world is moving faster towards Big Data and it is absolutely necessary to cope up with that. Today we will discuss one such feature called Approximate Query Processing. Approximate Query Processing SQL Server ships with Intelligent Query Processing out of the box with SQL Server 2019 installation. Approximate Query processing is a part of Intelligent Query Processing. Things we will be covering in this article – Understand the need for Approximation with Case Study Case Study 1: Railway Case Study 2: e-commerce How to use Approximate Query Processing Demo Code for Comparing Performance Results Limitations When to avoid Approximate Query Processing Understand the need for Approximate Query Processing Before using any technological feature, we must understand why we should use it? Should we jus...
Post a Comment
Read more

How to generate C# Class from SQL Server Table

C# Class from SQL Database Table There are multiple ways you can generate a C# class from your Database Table. We will be covering the following topics in today’s article. Generate Class with foreign key relation Generate Class with only entities Generate Class with foreign key relations For this we can simply use Entity Frameworks EDMX update feature which will generate our C# class from Database Tables. The output from EDMX will contain foreign key relations which we can see from the virtual interfaces like this -  public partial class AssetItem { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public AssetItem() { this .AssetItemDepreciations = new HashSet<AssetItemDepreciation>(); this .AssetTaxMappings = new HashSet<AssetTaxMapping>(); this .AssetVatMappings = new HashSet<AssetVatMapping>(); ...
Post a Comment
Read more

6 Letters for SQL Disaster Emergencies : RPO and RTO

 
Post a Comment
Read more