Every DBA, even a beginner, may walk through the SQL Server backup screen multiple times per day. It is mandatory that you know every single detail of every single option you have in the most repeatable task you could do as a DBA.

每个DBA，甚至是初学者，每天都可能多次浏览SQL Server备份屏幕。 您必须知道，作为DBA，您可以在最可重复的任务中拥有每个选项的每个细节。

In this article, I will be discussing every option available in full backup screen of SQL Server 2016.

在本文中，我将讨论SQL Server 2016完整备份屏幕中可用的每个选项。

数据库文件 (Database files)

When we talk about backing up SQL Server, we are talking about backing up the physical files that make up the data obviously.

当我们谈论备份SQL Server时，我们在谈论备份显然构成数据的物理文件。

Database backups traditionally have backed up two types of files, the MDF (main database file) and the NDF (secondary database file). You always have one MDF and 0 or more NDFs, depending on how you design your database. Full backups back up at least those two.

传统上，数据库备份已备份两种类型的文件，即MDF（主数据库文件）和NDF（辅助数据库文件）。 根据设计数据库的方式，您总是有一个MDF和0个或多个NDF。 完整备份至少备份这两个。

There is another file type LDF which is the log information file and it is backed up when you perform a transaction log backup.

还有另一个文件类型LDF，它是日志信息文件，在执行事务日志备份时将对其进行备份。

基本完整备份 (Basic full backup)

To perform a simple full backup, we can open SQL Server Management Studio and right-click on the database we want to back up, then choose tasks then click back up.

要执行简单的完整备份，我们可以打开SQL Server Management Studio，然后右键单击要备份的数据库，然后选择任务，然后单击备份。

You will end up on the following screen:

您将在以下屏幕上结束：

And this screen will be our focus, next.

接下来，这个屏幕将成为我们的重点。

备份数据库屏幕 (Back up database screen)

In this screen we can see three pages (tabs):

在此屏幕中，我们可以看到三个页面（标签）：

General page

一般页面

It is separated into two parts: source and destination.

它分为两个部分：源和目标。

In the source part you can see the following options:

在源代码部分中，您可以看到以下选项：

• Database: a combo box to select the database you want to back up. Since we have right clicked on SQL_SHACK database, it is shown by default but we can select any other database we want.

  数据库：一个组合框，用于选择要备份的数据库。 由于我们右键单击了SQL_SHACK数据库，因此默认情况下会显示该数据库，但我们可以选择所需的任何其他数据库。

   

  

   

• Recovery model: actually, this cannot be changed. This is an indicator of what is available to be backed up. Only databases in full and bulk-logged recovery model can be backed up using transaction log backup type. As we are talking here about only full back up, so in all recovery models, a full backup can be done. 恢复模型：实际上，这无法更改。 这表明可以备份哪些内容。 使用事务日志备份类型只能备份完全恢复和批量记录恢复模式的数据库。 正如我们在这里只讨论完整备份一样，因此在所有恢复模型中，都可以进行完整备份。
• 
   
• Backup type: 备份类型：

  Here you can choose the backup type you want:

  在这里，您可以选择所需的备份类型：

  • Full – backups everything in the database.

     

    完整–备份数据库中的所有内容。
  • Differential – backups only what is changed from last full backup.

     

    差异备份–仅备份与上次完整备份相比更改的内容。
  • Transaction log – backups transaction file

     

    交易日志–备份交易文件
  •  

     

    

     

• Copy-only backup: 仅复制备份：

  This option is very important and it is not very clear to many DBAs.

  此选项非常重要，对于许多DBA来说也不太清楚。

  Let me explain it in more details using one scenario:

  让我使用一种情况更详细地解释它：

  Considering you have a backup maintenance plan that takes backups on the following schedule:

  考虑到您有一个备份维护计划，该计划按以下时间表进行备份：

  • Full back up – every Friday at 11:59 PM

     

    全备份–每个星期五晚上11:59
  • Differential backup – every day at 11:00 PM

     

    差异备份–每天晚上11:00
  • Transactional log backup – every hour

     

    事务日志备份–每小时
   

  Next let’s assume, that you have a request to restore this database on another server, for example.

  接下来，假设您有一个请求，例如，要在另一台服务器上还原此数据库。

  It is a simple task; just a basic backup and copy the file then restore it to the target server. But you must be aware that you have a backup sequence, which means that every differential back and transactional log backup is depending on the last full backup taken. And by doing that simple task you broke the chain of backups because all of the differential and transactional log backups now will depend on that simple backup you take. So for any reason, if this backup is lost and you faced any type of disaster before the next backup, you are in trouble.

  这是一个简单的任务。 只是基本备份并复制文件，然后将其还原到目标服务器。 但是您必须知道您有一个备份序列，这意味着每个差异备份和事务日志备份都取决于上一次执行的完整备份。 通过执行该简单任务，您打破了备份链，因为现在所有差异日志和事务日志备份都将取决于您执行的简单备份。 因此，由于任何原因，如果丢失了该备份，并且在下一次备份之前您遇到了任何类型的灾难，则可能会遇到麻烦。

  Now, you will not be able to restore this database to meet you point in time objective as you only could restore the database to the time before that simple backup you had taken. And here comes the benefit of Copy-only backup option which enables you to take that simple backup anytime you need without affecting the backup sequence.

  现在，您将无法还原该数据库以满足您的时间点目标，因为您只能将数据库还原到进行该简单备份之前的时间。 而这带来了“仅复制”备份选项的好处，该选项使您可以在需要时随时进行简单备份，而不会影响备份顺序。

   
   
• Backup component: this option allows you to check if you want to backup the entire database or to choose specific files or filegroups to backup. 备份组件：此选项使您可以检查是否要备份整个数据库或选择要备份的特定文件或文件组。

There is a very important thing to note here, though. You cannot backup files or filegroups for a database in simple recovery model.

不过，这里有一件非常重要的事情要注意。 您无法在简单恢复模型中备份数据库的文件或文件组。

In full or bulk-logged recovery model, you can choose any file or filegroup you want to backup.

在完整记录或批量记录的恢复模型中，您可以选择要备份的任何文件或文件组。

And Microsoft has stated the reason for that as “read/write files must all be backed up together. This helps make sure that the database can be restored to a consistent point in time.” For more details check this article.

微软已经指出了这样做的原因，因为“读/写文件必须全部备份在一起。 这有助于确保可以将数据库还原到一致的时间点。” 有关更多详细信息，请查看本文 。

In the destination part, you can see you will define where you want to place your backup file and you have two options here.

在目标部分，您会看到将定义要放置备份文件的位置，这里有两个选项。

• Backup to disk: here you can choose the folder and file name for your backup on local disk or share location or your registered backup devices.

  备份到磁盘：在这里，您可以选择要在本地磁盘或共享位置或注册的备份设备上备份的文件夹和文件名。

   

  

   

  A small thing to be noted here – for backup performance and storage purposes you can split your back across multiple files.

  这里需要注意的一件事-为了备份性能和存储目的，您可以将其拆分为多个文件。

  You can get faster processing backups if the files were on different physical drives.

  如果文件位于不同的物理驱动器上，则可以更快地处理备份。

  Also, you will get smaller file sizes in case you need to fit them on a CD or DVD or you just want to make the copy across your network easier.

  此外，如果您需要将它们放入CD或DVD上，或者只是想简化整个网络中的副本，则文件大小会变小。

   

  

   

  Also, there is something you need to know here, as well. You can view the contents of your previous backup files to decide if you want to append to it or overwrite.

  另外，在这里您还需要了解一些内容。 您可以查看以前的备份文件的内容，以决定是要追加还是覆盖它。

   

  

   

   

  For this specific backup file, you can see that I have appended two backups to the same file two backup sets with their dates and all other details.

  对于此特定的备份文件，您可以看到我已将两个备份附加到同一个文件中，两个备份集及其日期和所有其他详细信息。

• Backup to URL: this is used when you want to store your backup file on Azure Blob Storage.

  备份到URL：当您要将备份文件存储在Azure Blob存储上时使用。

   

  

   

   

  I will discuss here what you need to perform this operation:

  我将在这里讨论执行此操作所需的条件：

  1.  

      

  2. Create Windows Azure Storage account

      

     创建Windows Azure存储帐户

  3.  

      

  4. Create root container on the windows azure storage. We can generate a Shared Access Signature token on a container, and grant access to objects on a specific container only

      

     在Windows Azure存储上创建根容器。 我们可以在容器上生成共享访问签名令牌，并仅授予对特定容器上对象的访问权限

  5.  

      

     • URL to the unique backup file name: even if it doesn’t exist yet, you must specify the name of the backup file in the URL like “http://SQLSHACK.blob.core.windows.net/backups/SQLSHACK_20180115.bak”

        

       唯一备份文件名称的URL：即使尚不存在，也必须在URL中指定备份文件的名称，例如“ http://SQLSHACK.blob.core.windows.net/backups/SQLSHACK_20180115.bak” ”
     • 
        
     • SQL server credential: this is an object that stores authentication info required to connect to a resource outside SQL Server. The credential stores either the name of the storage account and the storage account access key values or container URL, and its shared access signature token.

        

       SQL Server凭据：此对象存储连接到SQL Server外部资源所需的身份验证信息。 凭据存储存储帐户的名称和存储帐户访问键值或容器URL，以及其共享访问签名令牌。

摘要 (Summary)

SQL Server backups are the core of our job. You can easily get fired because of it. It is the first thing every DBA needs to know before starting his\her career as a DBA. I tried to keep it simple when discussing the very basic details for beginners. I hope this article has been informative for you.

SQL Server备份是我们工作的核心。 因此，您很容易被解雇。 这是每个DBA在开始其DBA职业生涯之前需要了解的第一件事。 在讨论初学者的基本细节时，我试图使其保持简单。 希望本文对您有所帮助。

参考资料 (References)

• Back up database (general page) 备份数据库（通用页）
• Select backup destination 选择备份目的地
• Create credential – authenticate to Azure Storage 创建凭据–向Azure存储进行身份验证

翻译自: https://www.sqlshack.com/walk-through-sql-server-2016-full-database-backup/