Prashanth Jayaram

Author
Recent Posts

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

SQL Interview questions on database backups, restores and recovery – Part IV

July 25, 2018 by Prashanth Jayaram

In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database. Also, discuss several T-SQL statements to derive most useful information with reference to data purge, database growth, backup report, restore history and more.

SQL interview questions on database backups, restores and recovery – Part III

July 25, 2018 by Prashanth Jayaram

So far, we’ve discussed a lot about database backup commands. In this article, we’ll discuss more on database restore or database recovery processes. When you initiate a restore process, it undergoes a series of internal stages to restore or recover the data to the specific point of time.

SQL interview questions on database backups, restores and recovery – Part II

July 25, 2018 by Prashanth Jayaram

In this article, we’ll walk through, some of the refined list of SQL Server backup-and-restore, (or recovery) interview Q&A. Also, it includes the high-level overview and links of the “stairway to backup-and-restore series” articles for detailed information. As you read through the lists of Q&A, you’ll learn most of the features and concepts of Backup-and-Restore operations.

Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio

July 19, 2018 by Prashanth Jayaram

In this 18^th article of the series, we will discuss the concepts of database backup-and-restore of SQL Server Docker containers using Azure Data Studio. Before proceeding, you need to have Docker engine installed and Azure Data Studio configured on your host machine.

This article covers the following topics:

Overview of Azure Data Studio (ADS)
How to use Azure Data Studio integrated terminal
Definition of Docker containers
Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the Azure Data Studio interface
And more…

Understanding Backup and Restore operations in SQL Server Docker Containers

July 12, 2018 by Prashanth Jayaram

In this 17^th article of the series (see the full article index at bottom), we will discuss the concepts of database backup-and-restore operations on SQL Server Docker containers. This is certainly the era of containers era and it is buzz right now, so let us understand the importance of backup-and-restore the databases on the Docker containers.

In-Memory Optimized database backup and restore in SQL Server

July 6, 2018 by Prashanth Jayaram

A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. A well-planned and well-tested backup-and-restore strategy always help to protect the organization with no data loss, or at least a minimum, from the many varieties of system and database failure. As such, it is recommended to understand the importance and implication of the backup-and-restore strategy.

Database Filegroup(s) and Piecemeal restores in SQL Server

June 22, 2018 by Prashanth Jayaram

So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15^th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process.

SQL Server Database Backup and Restore reports

June 14, 2018 by Prashanth Jayaram

In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file.

In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management.

Tail-Log Backup and Restore in SQL Server

May 31, 2018 by Prashanth Jayaram

A tail-log backup is a special type of transaction log backup. In this type of backup, the log records that have not been backed up are captured. This prevents data loss and maintains the log chain sequence.

SQL Server Database Backup and Restore operations using the Cloud

May 29, 2018 by Prashanth Jayaram

This article discusses the concept of SQL Server backup and the various components required to use the Microsoft Azure Blob storage service as a target for the backup. As we all know, disk and tapes were the default destinations of choice until the evolution of the cloud platform. In addition to this, now we can expand the native functionality of SQL Server backup to cloud storage; the Windows Azure Blob storage. This functionality was added first in SQL Server 2012. In general, backup and restore functionality to and from the cloud are similar to using disk or tape, with very few differences.

Backup Linux SQL Server databases using PowerShell and Windows task scheduler

May 22, 2018 by Prashanth Jayaram

This article is an in-depth guide on how PowerShell can be used to maintain and manage SQL backup on Linux and Windows machines.

Here’s an outline of what this article is all about:

Introduction
Technical specifications
How to load SQL Server modules on Windows machine
Security – Credential Management
The objectives of Backup and Restore
Library Linkage
How SQL Server 2017 backup feature is used on Linux
And more …

How to perform a page level restore in SQL Server

May 18, 2018 by Prashanth Jayaram

In this article in our stairway series on SQL Server backup and restore, we shall discuss the importance of understanding the internals of the page restoration for a database administrator. Most of the time, performing a page-level restore suffices for database availability. A good backup strategy is a key to recovery or restoration, and SQL Server provides us with an option to fix database pages at a granular level. Performing a page level restore in SQL Server is a clever option wherein the restoration of the entire VLDB database backup is not required.

Smart database backups in SQL Server 2017

May 14, 2018 by Prashanth Jayaram

So far, we’ve discussed several phases of backup that starts with planning, creating, strategizing and implementing. In this article, we are going to see how database administrators can define the strategy to improve backup performance and efficiently manage backups in SQL Server 2017. The following are the topics of discussion:

Discuss checkpoints
Discuss the enhancements made in the Dynamic Management View (DMV) sys.dm_db_file_space_usage for smart differential backups
Discuss the enhancements made for the Dynamic Management function (DMF) sys.dm_db_log_stats for smart transactional log backup
Understand the functioning of smart differential backup and its internals
Understand the Smart transaction log backup process and its internals
T-SQL scripts
And more…

SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques

May 7, 2018 by Prashanth Jayaram

Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.

Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.

Understanding Database snapshots vs Database backups in SQL Server

May 4, 2018 by Prashanth Jayaram

In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restoration operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.

Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

May 3, 2018 by Prashanth Jayaram

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

Backup and Restore (or Recovery) strategies for SQL Server database

April 26, 2018 by Prashanth Jayaram

One of the most important roles of a database administrator is to constantly protect the integrity of the databases and maintain the ability to recover quickly in case of a failure. In light of this, it’s critically important to have a backup-and-recovery strategy in place in order to be ready for an emergency.

A key responsibility of a database administrator is to ensure that a database is available whenever it’s needed, and prepare for various scenarios wherein the availability or the performance is impacted. Therefore, if a database, for whatever reason, gets corrupted, gets dropped, gets accidentally deleted, or goes into an unusable state, it is a database administrator’s responsibility to bring the database back up in a working state with little to no loss as per the defined service level agreements or government policies.

Understanding SQL Server Backup Types

April 19, 2018 by Prashanth Jayaram

SQL Server backups, in itself, is a vast subject; so vast, there are multiple books written about them. In this article, however, we are going to focus on the types of backups that are available to us, and understand how to pick what we need, and what aspects we base that decision on. This understanding would, in turn, help us decide our backup-and-restore strategy.

Following are the most common types of backups available in SQL Server:

Full
Differential
Transaction log
Tail Log backup

Understanding SQL Server database recovery models

April 10, 2018 by Prashanth Jayaram

A recovery model is a database configuration option that determines the type of backup that one could perform, and provides the ability to restore the data or recover it from a failure.

The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation.

Getting started building applications using SQL Server DevOps Tools

March 28, 2018 by Prashanth Jayaram

This article is part of the SQL Server Tools series, aimed at giving you an idea of the available tools and techniques to build applications using SQL Server tools. The first article of the series covers the rudimentary DevOps concepts and discusses on a high level, the overview of SQL Server tools that can be piped for DevOps operations.

Understanding the SQL Server Data Management Life Cycle

March 21, 2018 by Prashanth Jayaram

This is the second article in the “Backup and Restore (or Recovery) in SQL Server” stairway series (see the full TOC below). This article deals with the different phases of data management life cycle and it encompasses the following topics:

Introduction to data corruption
Defining data corruption and its causes
Discussion on the impact of data corruption
Explaining data prevention mechanisms
Data protection
And more…

SQL Server data security feature RLS (Row-Level Security) and GDPR

March 9, 2018 by Prashanth Jayaram

Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.

Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25^th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.

An overview of the process of SQL Server backup-and-restore

March 1, 2018 by Prashanth Jayaram

In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.

Top 8 new (or enhanced) SQL Server 2017 DMVs and DMFs for DBAs

January 17, 2018 by Prashanth Jayaram

Dynamic management views (DMVs) and dynamic management functions (DMFs) are system views and system functions that return metadata of the system state. On querying the related system objects, database administrators can understand the internals of SQL Server. It allows us to monitor the performance of the SQL Server instance, and diagnose issues with it.

SQL Server 2017 ships with a number of new and enhanced dynamic management views and dynamic management functions that will help DBAs monitor the health and performance of SQL Server instances. A few existing DMV’s such as sys.dm_os_sys_info and sys.dm_db_file_space_usage have been enhanced. Some have also been newly built and available only for SQL Server 2017.

The importance of Python in SQL Server Administration

January 8, 2018 by Prashanth Jayaram

Some of my previous articles on Python provided insight of the basics and the usage of Python in SQL Server 2017.

This article is an effort to collect all the missing pieces and try to showcase the importance of using Python programming in SQL Server.