了解SQL Server备份类型

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.

SQL Server备份本身就是一个广泛的主题。 如此庞大，有许多关于它们的书。 但是，在本文中，我们将重点介绍可供我们使用的备份类型，并了解如何选择所需的内容以及该决定基于哪些方面。 反过来，这种理解将有助于我们确定备份和还原策略。

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

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

1. Full
   充分
2. Differential
   微分
3. Transaction log
   交易记录
4. Tail Log backup
   尾日志备份

There are other backup types available as well:

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

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

完整备份 (Full backups)

A full backup, as the name implies, backs up everything. It is the foundation of any kind of backup. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.

顾名思义，完整备份可备份所有内容。 它是任何备份的基础。 这是一个完整的副本，其中存储了数据库的所有对象：表，过程，函数，视图，索引等。具有完整备份，您将能够轻松地以与数据库完全相同的形式还原数据库。备份时间。

A full backup creates a complete backup of the database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one single backup.

完全备份可创建数据库的完整备份以及事务日志的一部分，因此可以恢复数据库。 由于所有内容都包含在一个备份中，因此这可以采用最简单的数据库还原形式。

A full backup must be done at least once before any of the other types of backups can be run—this is the foundation for every other kind of backup.

完整备份必须至少执行一次，然后才能运行其他任何类型的备份，这是所有其他类型备份的基础。

How to create full database backup using T-SQL

如何使用T-SQL创建完整的数据库备份

The BACKUP DATABASE is the command used to create a full database backup. It requires at least two input parameters: the database name and the backup device.

BACKUP DATABASE是用于创建完整数据库备份的命令。 它至少需要两个输入参数：数据库名称和备份设备。

Following is the example for a full database backup to be stored in a device:

以下是将完整数据库备份存储在设备中的示例：

BACKUP DATABASE [SQLShackDemoATC]
To DISK='f:\PowerSQL\SQLShackDemoATC.BAK'
WITH FORMAT,
      MEDIANAME = 'Native_SQLServerBackup',
      NAME = 'Full-SQLShackDemoATC backup';

完整的数据库备份到多个文件 (Full Database Backup to Multiple files)

Sometimes in some instances, we’re limited by the amount of space we have. 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.

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

BACKUP DATABASE SQLShackDemoATC TO 
DISK = 'f:\PowerSQL\SQLShackDemoATC_1.BAK', 
DISK = 'f:\PowerSQL\SQLShackDemoATC_2.BAK', 
DISK = 'f:\PowerSQL\SQLShackDemoATC_3.BAK', 
DISK = 'f:\PowerSQL\SQLShackDemoATC_4.BAK' 
WITH INIT, NAME = 'FULL SQLShackDemoATC backup', STATS = 5

If you’d like to create a mirror copy of the backup file:

如果要创建备份文件的镜像副本：

BACKUP DATABASE ProdSQLShackDemo 
TO DISK = 'F:\PowerSQL\ProdSQLShackDemo_1.BAK'
MIRROR TO DISK =  'F:\PowerSQL\ProdSQLShackDemo_2.BAK'
WITH FORMAT

You can, in fact, have up to three mirror copies:

实际上，您最多可以拥有三个镜像副本：

BACKUP DATABASE ProdSQLShackDemo 
TO DISK = 'F:\PowerSQL\ProdSQLShackDemo_1.BAK'
MIRROR TO DISK =  'F:\PowerSQL\ProdSQLShackDemo_2.BAK'
MIRROR TO DISK =  'F:\PowerSQL\ProdSQLShackDemo_3.BAK'
MIRROR TO DISK =  'F:\PowerSQL\ProdSQLShackDemo_4.BAK'
WITH FORMAT
GO

差异备份 (Differential Backups)

A differential database backup is the superset of the last full backup and contains all changes that have been made since the last full backup. So, if there are very few transactions that have happened recently, a differential backup might be small in size, but if you have made a large number of transactions, the differential backup could be very large in size. 

差异数据库备份是上次完整备份的超集 ，其中包含自上次完整备份以来所做的所有更改。 因此，如果最近发生的事务很少，差异备份的大小可能会很小，但是如果您进行了大量事务，差异备份的大小可能会非常大。

As a differential backup doesn’t back up everything, the backup usually runs quicker than a full backup. A differential database backup captures the state of the changed extents at the time that backup was created. If you create a series of differential backups, a frequently-updated database is likely to contain different data in each differential. As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, it is recommended to take a new full backup, at set intervals, to establish a new differential base for the data.

由于差异备份不能备份所有内容 ，因此备份通常比完整备份运行得更快。 差异数据库备份捕获创建备份时更改范围的状态。 如果创建一系列差异备份，则频繁更新的数据库很可能在每个差异中包含不同的数据。 随着差异备份的大小增加，还原差异备份会大大增加还原数据库所需的时间。 因此，建议以设定的时间间隔进行新的完整备份，以为数据建立新的差异基准 。

Differential backups save storage space and the time it takes for a backup. However, as data changes over time, the differential backup size also increases. The longer the age of a differential backup and larger the size and at some point in time it may reach the size of the full backup. A large differential backup loses the advantages of a faster and smaller backup as it requires the full backup to be restored before restoring the recent differential backup. Typically, we would resto