sql还原数据库备份数据库_有关数据库备份，还原和恢复SQL面试问题–第二部分

最新推荐文章于 2023-06-30 23:40:36 发布

culuo4781

最新推荐文章于 2023-06-30 23:40:36 发布

阅读量623

点赞数

文章标签：数据库大数据 python java mysql

原文链接：https://www.sqlshack.com/sql-interview-questions-on-database-backups-restores-and-recovery-part-ii/

版权

sql还原数据库备份数据库

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.

在本文中，我们将逐步介绍一些经过精炼SQL Server备份和还原（或恢复）采访问答。此外，它还包括高级概述和“楼梯间到备份和还原系列”文章的链接，以获取详细信息。在阅读问答列表时，您将了解备份和还原操作的大多数功能和概念。

In this article, we’ll discuss the following topics:

在本文中，我们将讨论以下主题：

1.什么是数据库备份？ (1. What are database backups?)

The word “backup” refers to copying and archiving the data so that it may be used to restore in case of an event of data loss. In general, you should back up any work or data that can’t be replaced easily.

“备份”一词是指复制和存档数据，以便在发生数据丢失的情况下可以将其还原。通常，您应该备份所有无法轻松替换的工作或数据。

A database backup is a copy of data from the database, which can be used to reconstruct the data. A database backup is a process to protect the data from various disasters. Disasters cannot be completely prevented; the least we can do is to ensure we have everything that’s needed to get up and running as soon as we can.

数据库备份是数据库中数据的副本，可用于重建数据。数据库备份是一种保护数据免受各种灾难影响的过程。无法完全预防灾难；我们要做的至少是确保我们拥有尽快启动并运行所需的一切。

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

SQL Server备份和还原过程概述

2.在计划备份，还原和恢复策略时要考虑哪些因素？ (2. What are factors to consider while planning for backup, restore, and recovery strategy?)

It’s a broad topic to discuss but, some of the high-level points to be considered while defining good backup strategy include the following topics:

这是一个广泛讨论的主题，但是在定义良好的备份策略时要考虑的一些高级要点包括以下主题：

Rate or frequency at which data changes
数据更改的速率或频率
Measure the volume of online transaction processing
测量在线交易处理量
Measure the frequency of the schema or structural design changes
测量方案或结构设计变更的频率
Measure the frequency of database configuration change
测量数据库配置更改的频率
Understand data generation and data loading patterns
了解数据生成和数据加载模式
Know the nature of the data
了解数据的性质

For additional information: refer to the below link

SQL Server数据库的备份和还原（或恢复）策略

3.数据生命周期管理有哪些不同阶段？ (3. What are different stages of data life-cycle management?)

It’s important for any database administrator to understand the data lifecycle and the nature of the particular business, in order to have the ability to recover artifacts of business value from any sort of data disruptions.

任何数据库管理员都必须了解数据生命周期和特定业务的性质，以便能够从任何类型的数据中断中恢复业务价值工件，这一点很重要。

Data capture or generation
数据采集或生成
Data usage
数据使用
Data corruption
资料损坏
Data corruption prevention
防止数据损坏
Data protection
数据保护
Data archival
数据档案
Data purging
数据清除
Data retention
资料保留

For additional information: refer to the below link

了解SQL Server数据管理生命周期

4.如何使用本机工具在多服务器环境中计划或设置SQL Server备份和还原策略？ (4. How to do plan or setup SQL Server Backup and Restore strategy in a multi-server environment using native tools?)

In some cases, as part of company acquisition process, we may lead to revisit the review the existing the plans or we may also need to run through the steps to measure the current process. It is always advised to understand the importance and implication of business requirements. As each database may have different requirements based on the application it serves. The requirements may be based on:

在某些情况下，作为公司收购过程的一部分，我们可能会导致重新审查现有计划的审查，或者我们可能还需要逐步执行一些步骤来衡量当前过程。始终建议您了解业务需求的重要性和含义。由于每个数据库根据其所服务的应用程序可能有不同的要求。这些要求可能基于：

How frequently does the application access the database? Is there a specific off-peak period when the backups can be scheduled?
应用程序多久访问一次数据库？可以安排备份的特定非高峰时段吗？
How frequently does the data get changed? If the changes are too frequent, you may want to schedule incremental backups in between full backups. Differential backups also reduce the restoration time.
数据多久更改一次？如果更改太频繁，则可能要在完全备份之间安排增量备份。差异备份还减少了恢复时间。
If only a small part of a large database changes frequently, partial and/or file backups can be used.
如果大型数据库中只有一小部分频繁更改，则可以使用部分和/或文件备份。
Estimate the size of a full backup. Usually, the backup is smaller than the database itself, because it does not record the unused space in a database.
估计完整备份的大小。通常，备份比数据库本身要小，因为它不会在数据库中记录未使用的空间。

PowerShell comes into the rescue for most of the DBAs. It is really that simple enough to design to gather backup information with few lines of code.

对于大多数DBA而言，PowerShell都应运而生。确实如此简单，足以设计出几行代码来收集备份信息。

For additional information: refer to the below link

使用PowerShell和T-SQL在多服务器环境中规划SQL Server备份和还原策略

5.数据库恢复模型如何影响数据库备份？ (5. How does the database recovery model impact database backups?)

Database recovery model is the database configuration determines the type of database backup one could initiate on the database. It defines the state of the entries in the in transaction log files. Also, it provides the ways to restore or recover the database based on the configuration from varieties of failure.

数据库恢复模型是数据库配置决定了可以在数据库上启动的数据库备份的类型。它定义事务日志文件中条目的状态。此外，它还提供了基于各种故障的配置来还原或恢复数据库的方法。

SQL Server database recovery models:

SQL Server数据库恢复模型：

SIMPLE
简单
FULL
充分
Differential
微分

For additional information: refer to the below link.

了解SQL Server数据库恢复模型

6.解释SQL Server中不同类型的数据库备份？ (6. Explain the different types of database backup in SQL Server?)

The most common types of backups available in SQL Server:

SQL Server中可用的最常见的备份类型：

Full
充分
Differential
微分
Transaction log
交易记录
Tail Log backup
尾日志备份

There are other backup types available as well:

还有其他可用的备份类型：

Copy-only backup
仅复制备份
File backups
文件备份
Partial backups.
部分备份。

For additional information: refer to the below link

有关更多信息：请参阅以下链接

Understanding SQL Server Backup Types

了解SQL Server备份类型

7.如何确认每天进行备份？ (7. How can I verify that backups are occurring on a daily basis?)

You could rely on database backup reports. In general, database administrators are very much concerned with getting the database backup report on a daily basis and also alerts as per the Service Level Agreement. It’s considered as a prime role of DBAs to rely on the backup report to understand and how the backups are running.

您可以依靠数据库备份报告。通常，数据库管理员非常关注每天获取数据库备份报告以及根据服务水平协议发出警报。依靠备份报告来了解备份以及备份的运行方式被认为是DBA的主要角色。

The reports can be generated in many ways

报告可以通过多种方式生成

PowerShell scripts can be used to review the SQL Server error logs for backup failure events for the specific event id 18204,18210,3009,3017,3033, and 3021
PowerShell脚本可用于查看SQL Server错误日志以获取特定事件ID 18204、18210、3009、3017、3033和3021的备份失败事件
msdb.dbo.backupset for the backup information msdb.dbo.backupset以获取备份信息
PowerShell SMO library to pull the related backup information
PowerShell SMO库提取相关备份信息

For additional information: refer to the below link

SQL Server数据库备份和还原报告

8.数据库备份和数据库快照有什么区别？ (8. What is the difference between Database backup and Database snapshots?)

The database snapshots are directly dependent on the source structure of the database. Therefore, snapshots can never be a substitute for backup-and-restore strategy. For instance, if an entire database is lost or corrupted, it means, the source files become inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.

数据库快照直接取决于数据库的源结构。因此，快照绝不能替代备份和还原策略。例如，如果整个数据库丢失或损坏，则意味着源文件不一致。如果源文件不可用，快照将无法引用它们，因此快照恢复将无法实现。

For additional information, refer to the below link

有关其他信息，请参见下面的链接

Understanding Database snapshots vs Database backups in SQL Server

了解SQL Server中的数据库快照与数据库备份

9.存储备份和还原相关信息的系统表是什么？ (9. What are the system tables that store backup and restore related information?)

The MSDB database in-house several system tables that stores all the information related to the backup-and-restore operation. You can query the following system tables for finding backup-and-restore information.

MSDB数据库内部有几个系统表，这些表存储与备份和还原操作有关的所有信息。您可以查询以下系统表以查找备份和还原信息。

backupfile – Store information about all the backup of the data file or log file
backupfile –存储有关数据文件或日志文件的所有备份的信息
backupfilegroup -This gives an information about all the backed up filegroups
backupfilegroup-提供有关所有备份文件组的信息
backupmediafamily – Stores information about each media family
backupmediafamily –存储有关每个媒体系列的信息
backupmediaset – Stores information about each backup media set
backupmediaset –存储有关每个备份媒体集的信息
backupset – Stores information about each backup set
backupset –存储有关每个备份集的信息
restorefile – Stores information about each restored file
restorefile –存储有关每个已还原文件的信息
restorefilegroup – Stores information about each restored filegroup
restorefilegroup –存储有关每个已还原文件组的信息
restorehistory – Stores information about each restore operation
restorehistory –存储有关每个还原操作的信息

For additional information; refer to the below link

SQL Server数据库的备份和还原（或恢复）策略

10.为备份和还原SQL Server 2017添加了哪些新增强功能？ (10. What are the new enhancements added for Backup and Restore SQL Server 2017?)

SQL Server 2017, the enhanced the DMV’s and DMF’s which facilitates an extra column to measure the modified extents in a simpler way.

SQL Server 2017是增强的DMV和DMF的增强版，它有助于增加一列以更简单的方式测量修改后的范围。

DCM (Differential Changed Map) tracks and tells what pages are changed since the last full backup. The values could be leveraged to determine if you need to initiate a full or differential backup.

DCM（差异更改地图）跟踪并告知自上次完整备份以来哪些页面已更改。可以利用这些值来确定是否需要启动完整备份或差异备份。

For more information, refer to the below link

SQL Server 2017中的智能数据库备份

11.什么是SQL Server 2017中的智能T-Log备份？ (11. What is a smart T-Log backup in SQL Server 2017?)

In SQL Server 2017, enhancement has been made to the sys.dm_db_log_stats dynamic management function. This function returns a column log_since_last_log_backup_mb. Now, you have a better control the transactional log backup based on the amount of the data that has changed since last transactional log backup.

在SQL Server 2017中，对sys.dm_db_log_stats动态管理功能进行了增强。此函数返回一列log_since_last_log_backup_mb。 现在，您可以根据自上次事务日志备份以来已更改的数据量，更好地控制事务日志备份。

For more information, refer to the below link

SQL Server 2017中的智能数据库备份

12.假定数据库恢复模型已满。完整的数据库备份每周一次在晚上9点运行。星期日，差异备份每天晚上9点运行。周一至周六，以及每小时的事务日志备份。现在，数据库在星期五5:10 PM崩溃了。如何进行数据库的时间点恢复？ (12. Assume that the database recovery model is full. The full database backup runs every week at 9 PM. Sunday, differential backup runs daily at 9 PM. Monday to Saturday, and hourly transaction log backups. Now, database crashed on Friday 5:10 PM. How to do a point-in-time recovery of the database?)

Yes, the point-in-time recovery is possible.

是的，可以进行时间点恢复。

First, initiate a tail log backup
首先，启动尾日志备份
Now, restore recent Full database backup with NORECOVERY, i.e.is. Sunday 9 PM backup
现在，使用NORECOVERY即ieis恢复最近的完整数据库备份。周日晚上9点备份
Apply the Thursday night differential backup with NORECOVERY, i.e.is Thursday 9 PM. Differential backup
应用带有NORECOVERY的周四晚上差异备份，即周四晚上9点。差异备份
Next, Apply all the T-Log backups since Thursday Differential backup with NORECOVERY option
接下来，应用周四以来的所有T-Log备份使用NORECOVERY选项进行差异备份
Apply tail-log backup WITH RECOVERY and STOPAT options
应用带有RECOVERY和STOPAT选项的尾日志备份

For additional information, refer to the below link

有关其他信息，请参见下面的链接

Tail-Log Backup and Restore in SQL Server

SQL Server中的尾日志备份和还原

13.如何进行数据库刷新自动化？ (13. How to do database refresh automation?)

This can be done using several of the following available techniques:

可以使用以下几种可用技术来完成此操作：

Sqlcmd, Robocopy utility, and T-SQL
Sqlcmd，Robocopy实用程序和T-SQL

For more information, see the below article

讨论使用SQLCMD和SQL Server代理进行备份和还原自动化

PowerShell
电源外壳

For more information, see the below article

使用PowerShell和Windows任务计划程序备份Linux SQL Server数据库

Sqlpackage.exe tool
Sqlpackage.exe工具

For more information, see the below article

SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行SQL Server数据库还原

14.解释使用Cloud进行数据库备份和还原操作的过程吗？ (14. Explain the process of database backup and restore operations using the Cloud?)

The backup-to-cloud functionality was added 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. SQL Server database Backup to Azure Blob Storage is a process designed to perform almost like a backup device, such as disk or tape. During the backup or restore process, a URL is selected as a “device type” which in turn triggers a VDI (Virtual Backup Device Interface) client process. The process acts as an intermediary agent to send the database backup to the Azure Blob Storage.

SQL Server 2012中添加了“备份到云”功能。通常，与云之间的备份和还原功能与使用磁盘或磁带相似，差别很小。 SQL Server数据库到Azure Blob存储的备份是一个旨在像磁盘或磁带之类的备份设备一样执行的过程。在备份或还原过程中，将URL选择为“设备类型”，这又会触发VDI（虚拟备份设备接口）客户端过程。该过程充当中介代理，以将数据库备份发送到Azure Blob存储。

For more information, refer the below link

使用云SQL Server数据库备份和还原操作

15.在给定情况下，假定系统有3个驱动器，每个驱动器上有30 GB的可用空间。现在，如何为80 GB数据库执行数据库备份？可能吗？ (15. In a given situation, assume that the system has 3 drives with 30 GB of free space on each drive. Now, how can you perform a database backup for 80 GB database?. Is it possible?)

Yes, it is possible.

是的，有可能。

In some instances, we’re limited by the amount of space on the drives. What if we wanted to backup an entire database that is huge? Or what if we have to copy the backup files over the network? It might be a good idea in these cases to split the backup into smaller chunks—each being a separate file.

在某些情况下，我们受到驱动器上空间的限制。如果我们要备份整个巨大的数据库怎么办？或者，如果我们必须通过网络复制备份文件怎么办？在这些情况下，将备份分成较小的块可能是一个好主意-每个块都是一个单独的文件。

For more information, refer to the below link

有关更多信息，请参考以下链接

Understanding SQL Server Backup Types

了解SQL Server备份类型

16.解释零碎的还原操作吗？ (16. Explain piecemeal restore operation?)

Piecemeal restore helps with databases that contain multiple file-groups to be restored and recovered at multiple stages. This would give an option to customize the backup and restore (or recovery) solution.

零散还原可帮助包含多个文件组的数据库在多个阶段进行还原和恢复。这将提供自定义备份和还原（或恢复）解决方案的选项。

Consider a scenario; where we have a database with 3 file-groups, Primary, read-only and read-write file-groups. We actually need not perform backup for read-only file groups, here we can perform partial backups. We need to have a single backup copy of the read-only file-groups. Using the piecemeal process, we do have the option to restore required file groups but not all of the file groups are required to make the database online at a specific instance. It is always required to restore the primary filegroup but any secondary user-defined file groups are optional, at that point, while doing the restore. After the restore, one could get partial data and it’s available online and for the rest of the data, the users can wait, for a period of time, to recover other file-groups.

考虑一个场景；在这里，我们有一个包含3个文件组的数据库，分别是主文件组，只读文件和读写文件组。实际上，我们不需要为只读文件组执行备份，在这里我们可以执行部分备份。我们需要具有只读文件组的单个备份副本。使用零碎过程，我们确实可以选择还原所需的文件组，但是在特定实例上使数据库联机并不需要所有文件组。始终需要还原主文件组，但是在进行还原时，此时任何辅助用户定义的文件组都是可选的。还原后，可以获取部分数据，并且该数据可以在线使用，而对于其余数据，用户可以等待一段时间，以恢复其他文件组。

Database Filegroup(s) and Piecemeal restores in SQL Server

SQL Server中的数据库文件组和零碎还原

17.什么是数据库恢复阶段？对于内存优化对象，它有何不同？ (17. What are database recovery phases and how it is different for in-memory optimized objects?)

Database Recovery Phases

数据库恢复阶段

When SQL Server instances restart, each database goes through different recovery stages.

SQL Server实例重新启动时，每个数据库将经历不同的恢复阶段。

The different phases are:

不同的阶段是：

Analysis
分析
Redo
重做
Undo
撤消

Analysis: In this phase, the transaction log is analyzed to track the information about the last checkpoint and create the Dirty Page Table (DPT); this captures all the dirty-page details. In In-Memory OLTP engine, the analysis phase identifies the checkpoint inventory and prepares the system table with all the log entries and also its processes the associated file allocation log records

分析：在此阶段，将分析事务日志以跟踪有关最后一个检查点的信息并创建脏页表（DPT）；这将捕获所有脏页详细信息。在内存OLTP引擎中，分析阶段将识别检查点清单，并准备带有所有日志条目的系统表，并处理相关的文件分配日志记录

Redo: It’s a roll-forward phase. When this phase completes, the database comes online.

重做：这是前滚阶段。此阶段完成后，数据库将联机。

Here are the points to ponder:

以下是需要考虑的要点：

For disk-based tables, the database is moved to the current point-in-time and acquires locks taken by uncommitted transactions.
对于基于磁盘的表，数据库将移至当前时间点并获取未提交的事务获取的锁。
For memory-optimized tables, data from the data and delta file pairs are loaded into the memory and then update the data with the active transaction-log based on the last durable checkpoint. During this phase, disk and memory-optimized based object recovery run concurrently.
对于内存优化表，将数据和增量文件对中的数据加载到内存中，然后基于最后的持久检查点使用活动事务日志更新数据。在此阶段，基于磁盘和内存优化的对象恢复同时运行。
In SQL Server 2017, the redo phase of the memory-optimized tables (the associated data and delta files) is done in parallel. This injects faster times for database recovery process.
在SQL Server 2017中，内存优化表（相关数据和增量文件）的重做阶段是并行完成的。这为数据库恢复过程注入了更快的时间。
When the above operations are completed for both disk-based and memory-optimized tables, the database becomes online and available for access.
对于基于磁盘的表和针对内存优化的表，完成上述操作后，数据库将变为联机状态并可供访问。

Undo: It’s a rollback phase. It holds the list of the active transaction from the analysis phase basically undoing the transactions. This phase is not needed for memory-optimized tables since In-Memory OLTP doesn’t record any uncommitted transactions for memory-optimized tables.

撤消：这是一个回滚阶段。它保留了从分析阶段开始基本上撤消交易的活动交易的清单。对于内存优化表，不需要此阶段，因为内存中OLTP不会记录针对内存优化表的任何未提交的事务。

For additional information, refer to the below link

有关其他信息，请参见下面的链接

SQL Server Database Recovery Process Internals – database STARTUP Command

SQL Server数据库恢复过程内部–数据库STARTUP命令

For memory-optimized databases, refer the below link

对于内存优化的数据库，请参考以下链接

Backup and Restore of a SQL Server database with Memory-Optimized objects TBA

使用内存优化对象TBA备份和还原SQL Server数据库

18.如何在SQL Server Docker容器上执行数据库备份和还原操作？ (18. How to perform database backup and restore operation on SQL Server Docker containers?)

As long as the containers remain intact with the host, the data will remain safe even if the container is stopped or restarted. However, if you remove the container your databases get removed with it and it’ll be gone forever.

只要容器与主机完好无损，即使容器停止或重新启动，数据也将保持安全。但是，如果您删除该容器，则数据库也将随之删除，并且它将永远消失。

Let’s discuss the Ducker’s solution that keeps the data safe across containers. Using Docker data volume (-v) option, it is that simple to share the data. During the SQL Server container creation process, map to the SQL Server database file directory using –v parameter.

让我们讨论一下Ducker的解决方案，该解决方案可确保跨容器的数据安全。使用Docker数据量（-v）选项，共享数据非常简单。在SQL Server容器创建过程中，使用–v参数映射到SQL Server数据库文件目录。

For additional information, refer the below link

有关其他信息，请参见下面的链接

Backup and Restore using SQL Server Docker Containers TBA

使用SQL Server Docker容器TBA进行备份和还原

19.有哪些本地工具集可用于执行数据库备份和还原操作？ (19. What are the native toolset that are available to perform database backup and restore operation?)

SSMS
短信服务
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent 讨论使用SQLCMD和SQL Server代理进行备份和还原自动化
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行SQL Server数据库还原
SQL Ops Studio – Backup and Restore operations using SQL Ops Studio TBA
SQL Ops Studio –使用SQL Ops Studio TBA的备份和还原操作
Backup Linux SQL Server databases using PowerShell and Windows task scheduler 使用PowerShell和Windows任务计划程序备份Linux SQL Server数据库

20.数据库备份可使用的前10个跟踪标志是什么？ (20. What are the top 10 trace flags can be used with database backup?)

When used the following traceflags with 3605 traceflag, the output message to the errorlog.

当将以下跟踪标志与3605跟踪标志一起使用时，输出消息将发送到错误日志。

1806 – Turn off Instant-file-initialization
1806年–关闭即时文件初始化
3004 – Logs every step of backup/restore internal operation.
3004 –记录备份/还原内部操作的每个步骤。
3213 – Display the buffer and maxtransfersize information
3213 –显示缓冲区和最大传输大小信息
3023 – Enables CHECKSUM option for the BACKUP command
3023 –为BACKUP命令启用CHECKSUM选项
3226 – Prevent log backup entries written into the errorlog
3226 –防止将日志备份条目写入错误日志
3001 – Prevent log backup entries written into the MSDB tables.
3001 –防止将日志备份条目写入MSDB表。
3014 – Log more internal information for every backup operation
3014 –为每个备份操作记录更多内部信息
3042 – Bypasses the default backup compression pre-allocation algorithm
3042 –绕过默认的备份压缩预分配算法
3051 – Enables database backup to URL logging
3051 –启用数据库备份到URL日志记录
3608 – Prevents SQL Server from automatically starting and recovering any database except the master database
3608 –阻止SQL Server自动启动和恢复除主数据库之外的任何数据库