Prashanth Jayaram

Database Backup and Restore process in SQL Server – series intro

July 26, 2018 by

It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures.

Ready to dive-deep into Backup-and-Restore procedures? Let’s go!

Data is the key to your organization’s future, but if it’s outdated, irrelevant or hidden, then it’s no good. Maintaining and administrating the databases takes a lot of work. As DBAs, we often tend to automate most of the tasks.

After a quick refresher on database design concepts, followed by several hands-on examples and scenarios designed to teach how best the solutions works and how to apply it correctly. You’ll also learn about designing good backup and restore and recovery solutions.

As you move through the list of 20+ articles, you’ll see most of the features that include all of the various database Backup-and-Restore concepts, T-SQL programming techniques, learn PowerShell Scripting, implementation of backup solutions to Docker containers, data management using SQL Ops Studio, handling bacpac and dacpac files, ingress and egress data from Cloud and more. The articles are organized to make specific topics easy to find so that you can jump start depending on your skill level.

  1. Article 1: An overview of the process of SQL Server backup-and-restore
  2. Article 2: Understanding the SQL Server Data Management Life Cycle
  3. Article 3: Understanding SQL Server database recovery models
  4. Article 4: Understanding SQL Server Backup Types
  5. Article 5: Backup and Restore (or Recovery) strategies for SQL Server database
  6. Article 6: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
  7. Article 7: Understanding Database snapshots vs Database backups in SQL Server
  8. Article 8: SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
  9. Article 9: Smart database backups in SQL Server 2017
  10. Article 10: How to perform a page level restore in SQL Server
  11. Article 11: Backup Linux SQL Server databases using PowerShell and Windows task scheduler
  12. Article 12: SQL Server Database backup and restore operations using the Cloud
  13. Article 13: Tail-Log Backup and Restore in SQL Server
  14. Article 14: SQL Server Database Backup and Restore reports
  15. Article 15: Database Filegroup(s) and Piecemeal restores in SQL Server
  16. Article 16: Backup and Restore of a SQL Server database with Memory-Optimized objects
  17. Article 17: Backup and Restore using SQL Server Docker Containers
  18. Article 18: Backup and Restore operations with SQL Server Docker containers using SQL Ops Studio
  19. Article 19: Interview questions on SQL Server database backups, restores and recovery – Part I
  20. Article 20: Interview questions on SQL Server database backups, restores and recovery – Part II
  21. Article 21: Interview questions on SQL Server database backups, restores and recovery – Part III

Article 1: An overview of the process of SQL Server backup-and-restore

This article takes the reader through the database backup concepts and gives an overview to understand the basic design principles of database backup. Also, it describes the various type of backup destination and covers the definition of RPO and RTO at a very high-level.

  • Number of words: ~1000+
  • Time to read: ~5 minutes
  • Level: Beginner

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

Article 2: Understanding the SQL Server Data Management Life Cycle

In this article, we will discuss the different phases of Data Management Life-Cycle. This article is an effort to take a detailed look at all of the Data Generation, Data Analysis, Data Corruption, Data Prevention, Data Protection, Data Archival, Data Purging stages, and more.

  • Number of words: ~1000+
  • Time to read: ~5 minutes
  • Level: Beginner/Intermediate

Link: Understanding the SQL Server Data Management Life Cycle

Article 3: Understanding SQL Server database recovery models

In this article, we will walk-though the database recovery model. It’s a database setting or configuration option that determines the type of backup that one could perform. It provides an option or ability to restore the data or recover it from a failure.

Also, it comprises multi-server PowerShell and T-SQL script to capture the database recovery model, and more…

  • Number of words: ~1500+
  • Time to read: ~6 minutes
  • Level: Beginner/Intermediate

Link: Understanding SQL Server database recovery models

Article 4: Understanding SQL Server Backup Types

SQL Server backup is a vast topic; so vast; However, this article is an effort to outlay on the types of backups that are available, understand the importance, and how to pick what we need, and what aspects we base that decision on. This understanding of every option would, in turn, help us decide our backup-and-restore strategy. It also covers how to take standard and compressed database backups of all types.

  • Number of words: ~1500+
  • Time to read: ~7 minutes
  • Level: Beginner/ Intermediate

Link: Understanding SQL Server Backup Types

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

In this article, we will discuss the various factors to consider for designing a good backup-and-recovery strategy. This article takes the reader on how to use native T-SQL and PowerShell for data gathering tasks. Out of that, it also details the different ways to baseline the data for effective backup-and-restore planning.

The additional link for reference:

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

  • Number of words: ~1250+
  • Time to read: ~7 minutes
  • Level: Beginner/ Intermediate/Expert

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

Article 6: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

In the previous articles, we’ve gotten to know about more of design concepts and guidelines of database backup-and-restore operations. In this article, we start with the implementations. The database backup, copy operation using Robocopy utility, and database restore operation steps are scripted using sqlcmd mode. We work with process and teach how to schedule them using SQL Server agent.

Additional links:

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

Backup Linux SQL Server databases using PowerShell and Windows task scheduler

  • Number of words: ~1250+
  • Time to read: ~5 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent

Article 7: Understanding Database snapshots vs Database backups in SQL Server

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 restore operation is not a viable option. It also covers “the difference between database backup and database snapshot” topic.

  • Number of words: ~2250+
  • Time to read: ~10 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Understanding Database snapshots vs Database backups in SQL Server

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

In the article, we’ll discuss the “Database cross-platform tool—SqlPackage.exe”. This article the details the preparation of the PowerShell script to automate database restoration process using the SqlPackage; a SQL Server Data Tools suite.

  • Number of words: ~1000+
  • Time to read: ~5 minutes
  • Level: Beginner/ Intermediate/Expert

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

Article 9: Smart database backups in SQL Server 2017

So far, we’ve discussed several concepts of backup-and-restore that start 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. Also, briefs the concepts of indirect checkpoints, DCM, DMVs, and DMFs.

  • Number of words: ~2000+
  • Time to read: ~5 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Smart database backups in SQL Server 2017

Article 10: How to perform a page level restore in SQL Server

The pre-requisite for an efficient restore operation is always a good backup. In this article, we will walk-through, the concepts of page-level restore; what are the ways the data gets corrupted; brief introduction of XVI32.exe tool; how to deal with the corruption; how to do a page-level restore with SSMS and T-SQL interface, and more.

  • Number of words: ~1000+
  • Time to read: ~4 minutes
  • Level: Beginner/ Intermediate/Expert

Link: How to perform a page level restore in SQL Server

Article 11: Backup Linux SQL Server databases using PowerShell and Windows task scheduler

This article takes the reader through the concepts and in-depth guide on how PowerShell provides a platform to maintain and manage SQL Server database backup on cross-platform systems. It covers the technical details of how to load the SQL Server modules, manage security using .NET class libraries, and more.

  • Number of words: ~2000+
  • Time to read: ~5 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Backup Linux SQL Server databases using PowerShell and Windows task scheduler

Article 12: SQL Server Database backup and restore operations using the Cloud

This article introduces the administrators to what Offsite and Onsite are; what is Cloud backup; how it’s implemented with T-SQL or SSMS. It covers the required technical specification to be met before jump start into the Cloud backup. We will walk-through the creation of Azure account, Storage container configuration, Credential authorization, GUI tools to perform database backup and restore, Implementation of database backup and restore using T-SQL and PowerShell, and more.

  • Number of words: ~2000+
  • Time to read: ~10 minutes
  • Level: Beginner/ Intermediate/Expert

Link : SQL Server Database backup and restore operations using the Cloud

Article 13: Tail-Log Backup and Restore in SQL Server

This article covers how to take Tail-log backups, and then use them in conjunction with other database backup types. Also, it details the steps to restore or recover a database to a particular point- in-time. They also cover common Tail-log scenarios and how to resolve them.

  • Number of words: ~1000+
  • Time to read: ~5 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Tail-Log Backup and Restore in SQL Server

Article 14: SQL Server Database Backup and Restore reports

In this article, we will discuss the importance of database backup reports and how to generate the report using T-SQL and PowerShell. Also, it covers the important baseline metrics of the report.

  • Number of words: ~2000+
  • Time to read: ~8 minutes
  • Level: Beginner/ Intermediate/Expert

Link: SQL Server Database Backup and Restore reports

Article 15: Database Filegroup(s) and Piecemeal restores in SQL Server

This article explains the de-facto details of the database file and filegroup architectures that enable file-based backup-and-restore and the process of capturing the necessary file backups and transaction-log backups, and using them in various restore operations.

  • Number of words: ~2000+
  • Time to read: ~8 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Database Filegroup(s) and Piecemeal restores in SQL Server

Article 16: Backup and Restore of a SQL Server database with Memory-Optimized objects

In this article, we will discuss High-level components, concepts of Memory-optimized database backup, and the recovery phases. It also details the internals of In-Memory OLTP engine and transaction logging mechanism, and more.

  • Number of words: ~2500+
  • Time to read: ~8 minutes
  • Level: Beginner/ Intermediate/Expert

Link: In-Memory Optimized database backup and restore in SQL Server

Article 17: Backup and Restore using SQL Server Docker Containers

This article would help the reader to perform database backup on the SQL Server Docker containers. It discusses the different scenario of data persistence and data externalization process.

  • Number of words: ~2000+
  • Time to read: ~8 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Understanding Backup and Restore operations in SQL Server Docker Containers

Article 18: Backup and Restore operations with SQL Server Docker containers using SQL Ops Studio

In this article, you will learn how to use SQL Operations studio (Preview) to do backup a database, view the status of the database backup with backup history tab, how to restore a database and integrated terminal, and more.

  • Number of words: ~1000+
  • Time to read: ~5 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio

Article 19: Interview questions on SQL Server database backups, restores and recovery – Part I

Finally, these articles provide a quick overview and reference of backup and restore commands, illustrated with definition, important tips and examples formulated as potential interview questions.

  • Number of words: ~2500+
  • Time to read: ~30 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Interview questions on SQL Server database backups, restores and recovery – Part I

Article 20: Interview questions on SQL Server database backups, restores and recovery – Part II

Finally, these articles provide a quick overview and reference of backup and restore commands, illustrated with definition, important tips and examples formulated as potential interview questions.

  • Number of words: ~2500+
  • Time to read: ~30 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Interview questions on SQL Server database backups, restores and recovery – Part II

Article 21: Interview questions on SQL Server database backups, restores and recovery – Part III

Finally, these articles provide a quick overview and reference of backup and restore commands, illustrated with definition, important tips and examples formulated as potential interview questions.

  • Number of words: ~2500+
  • Time to read: ~30 minutes
  • Level: Beginner/ Intermediate/Expert

Link: Interview questions on SQL Server database backups, restores and recovery – Part II


Prashanth Jayaram

Prashanth Jayaram

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
Prashanth Jayaram
Backup and restore, Training

About Prashanth Jayaram

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

4,933 Views