SQL Server中的数据库备份和还原过程–系列简介
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.
现在是时候从“ DBA”转向“ DBA专家”了。 本深入指南探讨了使用可用工具和技术建立良好的备份和还原策略所需的数据库备份和还原功能和技能的重要性。 在此过程中，您将获得一些有趣的见解以及数据库备份和还原过程的大多数概念。
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.
数据是组织未来的关键，但是，如果数据过时，不相关或隐藏，那就不好了。 维护和管理数据库需要大量工作。 作为DBA，我们通常倾向于自动化大多数任务。
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.
在浏览20余篇文章的列表时，您将看到大多数功能，其中包括所有各种数据库备份和还原概念，T-SQL编程技术，学习PowerShell脚本编写，对Docker容器的备份解决方案的实现，使用SQL Ops Studio进行数据管理，处理bacpac和dacpac文件，来自Cloud的入口和出口数据等。 文章经过整理，使特定主题易于查找，因此您可以根据自己的技能水平快速入门。
- Article 1: An overview of the process of SQL Server backup-and-restore 第1条：SQL Server备份和还原过程概述
- Article 2: Understanding the SQL Server Data Management Life Cycle 第2条：了解SQL Server数据管理生命周期
- Article 3: Understanding SQL Server database recovery models 第3条：了解SQL Server数据库恢复模型
- Article 4: Understanding SQL Server Backup Types 第4条：了解SQL Server备份类型
- Article 5: Backup and Restore (or Recovery) strategies for SQL Server database 第5条：SQL Server数据库的备份和还原（或恢复）策略
- Article 6: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent 第6条：讨论使用SQLCMD和SQL Server代理进行备份和还原自动化
- Article 7: Understanding Database snapshots vs Database backups in SQL Server 第7条：了解SQL Server中的数据库快照与数据库备份
- Article 8: SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques 第8条：SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行SQL Server数据库还原
- Article 9: Smart database backups in SQL Server 2017 第9条：SQL Server 2017中的智能数据库备份
- Article 10: How to perform a page level restore in SQL Server 第10条：如何在SQL Server中执行页面级还原
- Article 11: Backup Linux SQL Server databases using PowerShell and Windows task scheduler 第11条：使用PowerShell和Windows任务计划程序备份Linux SQL Server数据库
- Article 12: SQL Server Database backup and restore operations using the Cloud 第12条：使用云SQL Server数据库备份和还原操作
- Article 13: Tail-Log Backup and Restore in SQL Server 第13条：SQL Server中的尾日志备份和还原
- Article 14: SQL Server Database Backup and Restore reports 第14条：SQL Server数据库备份和还原报告
- Article 15: Database Filegroup(s) and Piecemeal restores in SQL Server 第15条：SQL Server中的数据库文件组和逐行还原
- Article 16: Backup and Restore of a SQL Server database with Memory-Optimized objects 第16条：具有内存优化对象SQL Server数据库的备份和还原
- Article 17: Backup and Restore using SQL Server Docker Containers 第17条：使用SQL Server Docker容器进行备份和还原
- Article 18: Backup and Restore operations with SQL Server Docker containers using SQL Ops Studio 第18条：使用SQL Ops Studio使用SQL Server Docker容器进行备份和还原操作
- Article 19: Interview questions on SQL Server database backups, restores and recovery – Part I 第19条：关于SQL Server数据库备份，还原和恢复的访谈问题–第一部分
- Article 20: Interview questions on SQL Server database backups, restores and recovery – Part II 第20条：关于SQL Server数据库备份，还原和恢复的访谈问题–第二部分
- Article 21: Interview questions on SQL Server database backups, restores and recovery – Part III 第21条：有关SQL Server数据库备份，还原和恢复的面试问题–第三部分
第1条： SQL Server备份和还原过程概述 (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.
本文向读者介绍了数据库备份的概念，并概述了数据库备份的基本设计原理。 此外，它还描述了各种类型的备份目标，并在非常高的层次上介绍了RPO和RTO的定义。
- Number of words: ~1000+ 字数：〜1000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner 级别：初学者
Link: An overview of the process of SQL Server backup-and-restore
第2条： 了解SQL Server数据管理生命周期 (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+ 字数：〜1000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/Intermediate 级别：初学者/中级
Link: Understanding the SQL Server Data Management Life Cycle
第3条： 了解SQL Server数据库恢复模型 (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…
此外，它包括多服务器PowerShell和T-SQL脚本以捕获数据库恢复模型，等等。
- Number of words: ~1500+ 字数：〜1500 +
- Time to read: ~6 minutes 阅读时间：〜6分钟
- Level: Beginner/Intermediate 级别：初学者/中级
Link: Understanding SQL Server database recovery models
第4条： 了解SQL Server备份类型 (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.
SQL Server备份是一个广泛的主题。 如此巨大 但是，本文的工作是花费可用的备份类型，了解其重要性，以及如何选择我们需要的内容以及该决定基于哪些方面。 对每个选项的了解反过来将有助于我们确定备份和还原策略。 它还介绍了如何进行所有类型的标准和压缩数据库备份。
- Number of words: ~1500+ 字数：〜1500 +
- Time to read: ~7 minutes 阅读时间：〜7分钟
- Level: Beginner/ Intermediate 级别：初学者/中级
Link: Understanding SQL Server Backup Types
链接： 了解SQL Server备份类型
第5条： SQL Server数据库的备份和还原（或恢复）策略 (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.
在本文中，我们将讨论设计良好的备份和恢复策略时要考虑的各种因素。 本文向读者介绍了如何使用本机T-SQL和PowerShell执行数据收集任务。 除此之外，它还详细介绍了为有效的备份和还原计划制定基准数据的不同方法。
The additional link for reference:
附加链接供参考：
使用PowerShell和T-SQL在多服务器环境中规划SQL Server备份和还原策略
- Number of words: ~1250+ 字数：〜1250 +
- Time to read: ~7 minutes 阅读时间：〜7分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Backup and Restore (or Recovery) strategies for SQL Server database
链接： SQL Server数据库的备份和还原（或恢复）策略
第6条： 讨论使用SQLCMD和SQL Server代理进行备份和还原自动化 (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.
在前面的文章中，我们已经了解了更多有关数据库备份和还原操作的设计概念和准则。 在本文中，我们从实现开始。 数据库备份，使用Robocopy实用程序进行的复制操作以及数据库还原操作步骤均使用sqlcmd模式编写脚本。 我们处理流程，并讲授如何使用SQL Server代理计划它们。
Additional links:
附加链接：
SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行SQL Server数据库还原
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
使用PowerShell和Windows任务计划程序备份Linux SQL Server数据库
- Number of words: ~1250+ 字数：〜1250 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
链接： 讨论使用SQLCMD和SQL Server代理进行备份和还原自动化
第7条： 了解SQL Server中的数据库快照与数据库备份 (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+ 字数：〜2250 +
- Time to read: ~10 minutes 阅读时间：〜10分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Understanding Database snapshots vs Database backups in SQL Server
第8条： SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行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.
在本文中，我们将讨论“数据库跨平台工具SqlPackage.exe”。 本文详细介绍了PowerShell脚本的准备工作，以使用SqlPackage自动执行数据库还原过程。 SQL Server数据工具套件。
- Number of words: ~1000+ 字数：〜1000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
链接： SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行SQL Server数据库还原
第9条： SQL Server 2017中的智能数据库备份 (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.
到目前为止，我们已经讨论了备份和还原的几个概念，这些概念从计划，创建，制定战略和实施开始。 在本文中，我们将了解数据库管理员如何在SQL Server 2017中定义提高备份性能和有效管理备份的策略。此外，还将简要介绍间接检查点，DCM，DMV和DMF的概念。
- Number of words: ~2000+ 字数：〜2000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Smart database backups in SQL Server 2017
第10条： 如何在SQL Server中执行页面级还原 (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.
有效还原操作的前提条件始终是良好的备份。 在本文中，我们将逐步介绍页面级还原的概念； 数据损坏的方式是什么？ XVI32.exe工具的简要介绍； 如何处理腐败； 如何使用SSMS和T-SQL接口等进行页面级还原。
- Number of words: ~1000+ 字数：〜1000 +
- Time to read: ~4 minutes 阅读时间：〜4分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: How to perform a page level restore in SQL Server
第11条： 使用PowerShell和Windows任务计划程序备份Linux 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.
本文为读者提供有关PowerShell如何提供平台来维护和管理跨平台系统上SQL Server数据库备份的概念和深入指南。 它涵盖了有关如何加载SQL Server模块，使用.NET类库管理安全性等技术细节。
- Number of words: ~2000+ 字数：〜2000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Backup Linux SQL Server databases using PowerShell and Windows task scheduler
链接： 使用PowerShell和Windows任务计划程序备份Linux SQL Server数据库
第12条： 使用云SQL Server数据库备份和还原操作 (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.
本文向管理员介绍“非现场”和“现场”。 什么是云备份？ 如何使用T-SQL或SSMS实施。 它涵盖了在开始进入云备份之前要满足的必需技术规范。 我们将逐步介绍Azure帐户的创建，存储容器配置，凭据授权，执行数据库备份和还原的GUI工具，使用T-SQL和PowerShell执行数据库备份和还原等。
- Number of words: ~2000+ 字数：〜2000 +
- Time to read: ~10 minutes 阅读时间：〜10分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link : SQL Server Database backup and restore operations using the Cloud
第13条： SQL Server中的尾日志备份和还原 (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+ 字数：〜1000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Tail-Log Backup and Restore in SQL Server
第14条： 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.
在本文中，我们将讨论数据库备份报告的重要性以及如何使用T-SQL和PowerShell生成报告。 此外，它涵盖了报告的重要基准指标。
- Number of words: ~2000+ 字数：〜2000 +
- Time to read: ~8 minutes 阅读时间：〜8分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: SQL Server Database Backup and Restore reports
第15条： SQL Server中的数据库文件组和逐行还原 (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+ 字数：〜2000 +
- Time to read: ~8 minutes 阅读时间：〜8分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Database Filegroup(s) and Piecemeal restores in SQL Server
第16条： 具有内存优化对象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.
在本文中，我们将讨论高级组件，内存优化的数据库备份的概念以及恢复阶段。 它还详细介绍了内存中OLTP引擎的内部结构和事务记录机制等。
- Number of words: ~2500+ 字数：〜2500 +
- Time to read: ~8 minutes 阅读时间：〜8分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: In-Memory Optimized database backup and restore in SQL Server
第17条： 使用SQL Server Docker容器进行备份和还原 (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.
本文将帮助读者在SQL Server Docker容器上执行数据库备份。 它讨论了数据持久性和数据外部化过程的不同情况。
- Number of words: ~2000+ 字数：〜2000 +
- Time to read: ~8 minutes 阅读时间：〜8分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Understanding Backup and Restore operations in SQL Server Docker Containers
链接： 了解SQL Server Docker容器中的备份和还原操作
第18条： 使用SQL Ops Studio使用SQL Server Docker容器进行备份和还原操作 (Article 18: Backup and Restore operations with SQL Server Docker containers using SQL Ops Studio)
In this article, you will learn how to use Azure Data 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.
在本文中，您将学习如何使用Azure Data Studio（Preview）进行数据库备份，通过“备份历史记录”选项卡查看数据库备份的状态，如何还原数据库和集成终端，以及更多。
- Number of words: ~1000+ 字数：〜1000 +
- Time to read: ~5 minutes 阅读时间：〜5分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio
链接： 使用Azure Data Studio在Docker容器上使用SQL Server 2017进行备份和还原操作
第19条： 关于SQL Server数据库备份，还原和恢复的访谈问题–第一部分 (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+ 字数：〜2500 +
- Time to read: ~30 minutes 阅读时间：〜30分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Interview questions on SQL Server database backups, restores and recovery – Part I
链接： 采访有关SQL Server数据库备份，还原和恢复的问题–第一部分
第20条： 关于SQL Server数据库备份，还原和恢复的访谈问题–第二部分 (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+ 字数：〜2500 +
- Time to read: ~30 minutes 阅读时间：〜30分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Interview questions on SQL Server database backups, restores and recovery – Part II
链接： 采访有关SQL Server数据库备份，还原和恢复的问题–第二部分
第21条： 有关SQL Server数据库备份，还原和恢复的面试问题–第三部分 (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+ 字数：〜2500 +
- Time to read: ~30 minutes 阅读时间：〜30分钟
- Level: Beginner/ Intermediate/Expert 级别：初学者/中级/专家
Link: Interview questions on SQL Server database backups, restores and recovery – Part II
链接： 采访有关SQL Server数据库备份，还原和恢复的问题–第二部分
翻译自: https://www.sqlshack.com/database-backup-and-restore-process-in-sql-server-series-intro/