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

How to encrypt an entire excel file using AES-128 encryption

What Is AES? AES stands for Advanced Encryption Standard. It is a symmetric block cipher that is used by the U.S. government to protect classified information. AES is used worldwide to protect classified data around the world. AES is essential in cybersecurity, electronic data protection, and computer security. Variations of AES AES is used in three block cipher versions namely AES 128, AES 192, AES 256. AES 128 uses 128-bit key length to encrypt and decrypt block messages. This is a symmetric secret key which means it uses same secret key for encrypting and decrypting message blocks. AES Encryption of Excel File Today we will AES 128 to encrypt an Excel File. The excel file looks like the following image. To encrypt this excel file, we will use C#. First, we need to create a console project. Let's name the project ExcelEncryption. First, we try to understand what we need to achieve. We need a KEY and Initialization Vector (IV) pair for AES 128 encryption. Let’s say, th
Post a Comment
Read more

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 start with
Post a Comment
Read more