本文共 8608 字，大约阅读时间需要 28 分钟。
The SQL Server backup provides an important solution for protecting critical data that is stored in SQL databases. And in order to minimize the risk of data loss, you need to make sure that you back up your databases regularly taking into consideration the changes applied to your data. It is a best practice to test your backups by restoring random backup files to a test environment and check that the backup files are not corrupted.
SQL Server备份为保护存储在SQL数据库中的关键数据提供了重要的解决方案。 为了最大程度地减少数据丢失的风险，您需要确保定期备份数据库，同时考虑应用于数据的更改。 最佳做法是通过将随机备份文件还原到测试环境来测试备份，并检查备份文件是否未损坏。
In addition to the normal disaster of data loss, the DBA can benefits from the backups if there is a media failure in one of the disks or any hardware damage, an accidental drop or delete applied by one of the users or usually copy the data from one server to another one for purposes such as setting up mirroring site or Always On Availability Groups.
除了正常的数据丢失灾难之外，如果其中一个磁盘发生介质故障或任何硬件损坏，用户之一意外地删除或删除数据库或通常从数据库复制数据，则DBA可以从备份中受益。从一台服务器到另一台服务器，例如用于设置镜像站点或始终可用组。
Before scheduling the backup job, you need to have an estimation of how much disk space will be used by the database full backup. Also, you need to have an initial estimation of the database size increment, because when the database size increases, the full database backups will require more storage space.
在计划备份作业之前，您需要估计数据库完全备份将使用多少磁盘空间。 另外，您还需要对数据库大小的增量进行初步估算，因为当数据库大小增加时，完整的数据库备份将需要更多的存储空间。
It is better first to estimate how much disk space requires for your database full database backup. The backup operation copies the data in the database to the backup file, this contains only the used data space only in your database and not any unused one. Which is usually smaller than the database size. In order to estimate the size of the database full backup you can use the sp_spaceused system stored procedure, that will display the number of rows, disk space reserved, and disk space used by a table, indexed view, or displays the disk space reserved and used by the database depending on the parameters.
最好首先估计数据库完整数据库备份需要多少磁盘空间。 备份操作将数据库中的数据复制到备份文件，该文件仅包含数据库中已使用的数据空间，而不包含任何未使用的数据空间。 通常小于数据库的大小。 为了估计数据库完全备份的大小，可以使用sp_spaceused系统存储过程，该过程将显示行数，保留的磁盘空间以及表，索引视图使用的磁盘空间，或者显示保留的磁盘空间和由数据库使用，具体取决于参数。
SQL Server offers many backup types, which depends on the recovery model of the database – that controls how the transaction log is managed in your database: Full backups, Differential backups, File backups, Filegroup backups and Transaction log backups.
SQL Server提供了许多备份类型，这些类型取决于数据库的恢复模型-控制数据库中事务日志的管理方式：完全备份，差异备份，文件备份，文件组备份和事务日志备份。
As a database administrator, you should make sure that each database is backed up successfully and on time. SQL Server backup is simple to be performed via SQL Server Management Studio, using T-SQL BACKUP DATABASE command or PowerShell command Backup-SqlDatabase cmdlet. But in the case of large databases, it takes you long time to complete the backup process, you don’t have enough space to complete the backup to a particular drive or it is difficult to copy this large backup file via the network or to the backup media.
作为数据库管理员，您应确保每个数据库都已按时成功备份。 SQL Server备份是简单地通过SQL Server Management Studio中进行的，使用T-SQL BACKUP DATABASE命令或PowerShell命令备份- SqlDatabase cmdlet。 但是对于大型数据库，您需要花费很长时间才能完成备份过程，您没有足够的空间来完成对特定驱动器的备份，或者很难通过网络或将大型备份文件复制到特定驱动器。备份媒体。
A possible idea to overcome the large database backup space and time issues is to divide the database backup to multiple files and reducing the time necessary to perform the database backups and to use the available space on multiple drives. What make this process faster is that you have the ability to write to multiple files at the same time and therefore split up the workload using multiple threads, as well as having smaller files that can be moved across the network or copied to the backup media. Another advantage of splitting the backup to multiple files is getting better I/O throughput.
解决大型数据库备份空间和时间问题的一种可能想法是将数据库备份划分为多个文件，并减少执行数据库备份和使用多个驱动器上的可用空间所需的时间。 使此过程更快的原因是，您能够同时写入多个文件，因此可以使用多个线程来分配工作负载，并且具有可以在网络上移动或复制到备份介质的较小文件。 将备份拆分为多个文件的另一个优点是获得了更好的I / O吞吐量。
In order to perform the backup process to multiple files from the SQL Server Management Studio, right-click your database, choose backup from the tasks list as below:
为了对SQL Server Management Studio中的多个文件执行备份过程，请右键单击数据库，然后从任务列表中选择备份，如下所示：
In the Backup Databases dialog box below, specify where to keep the database backup files by clicking on the Add button, under Destination. What differs here is that we need to specify more than one file to back up the SQLShackDemo database to. In our example, the first backup file resides on the D drive, and the second backup file resides on the C drive, where each backup file will be of identical size.
在下面的“备份数据库”对话框中，单击“目标”下的“添加”按钮，指定将数据库备份文件保留在何处。 此处的不同之处在于，我们需要指定多个文件来将SQLShackDemo数据库备份到其中。 在我们的示例中，第一个备份文件位于D驱动器上，第二个备份文件位于C驱动器上，每个备份文件的大小均相同。
If you are using SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 or SQL Server 2012 Standard and Enterprise Editions, you can even take benefits of the feature to reduce the database backup size, from Options tap as follows:
如果您使用的是SQL Server 2008 Enterprise Edition或SQL Server 2008 R2或SQL Server 2012 Standard和Enterprise Edition，则甚至可以利用“ 功能来减少数据库备份的大小，方法如下：
In the Set backup compression option, you can choose Use the default server setting which will take the setting already set in the backup compression default server-configuration option. Or choose Compress backup that will compress the backup, regardless of the server-level default, or finally choose Do not compress backup which will create an uncompressed backup, regardless of the server-level default.
在“设置备份压缩”选项中，可以选择“ 使用默认服务器设置” ，该设置将采用在“ 备份压缩默认服务器配置”选项中已经设置的设置。 或选择“ 压缩备份 ”，而不考虑服务器级别的默认值，或者选择“不压缩备份” ，这将创建未压缩的备份，而不考虑服务器级别的默认值。
From the above Backup dialog screen, click OK button to start the multiple files backup for the database. A dialog box will appear once the backup is finished successfully.
在上面的“备份”对话框屏幕中，单击“确定”按钮以启动数据库的多个文件备份。 成功完成备份后，将出现一个对话框。
You can perform the same backup process to multiple files using the below T-SQL script:
您可以使用以下T-SQL脚本对多个文件执行相同的备份过程：
BACKUP DATABASE [SQLShackDemo] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQLShackDemo.bak', DISK = N'D:\ Backup\SQLShackDemo2.bak' WITH NOFORMAT, NOINIT, NAME = N'SQLShackDemo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
Now, let’s try to restore our database from the multiple backup files we took previously. From the SQL Server Management Studio; right-click Databases node and select the Restore Database option. In the Restore Database dialog box below, Specify the Source where the backup files located and select the checkbox under Restore plan. Here you need to specify both backup files to restore the database successfully.
现在，让我们尝试从先前获取的多个备份文件中还原数据库。 来自SQL Server Management Studio； 右键单击“数据库”节点，然后选择“还原数据库”选项。 在下面的“还原数据库”对话框中，指定备份文件所在的源，然后选中“还原计划”下的复选框。 在这里，您需要指定两个备份文件才能成功还原数据库。
If you don’t specify all the backup files which the full backup process generated, you would end up with the error message displayed below:
如果未指定完整备份过程生成的所有备份文件，则最终将显示以下错误消息：
Finally, click OK in the Restore Database dialog box to restore the database from the specified backup files. A dialog box will appear once the restore is completed successfully.
最后，在“还原数据库”对话框中单击“确定”，以从指定的备份文件还原数据库。 恢复成功完成后，将出现一个对话框。
Using the T-SQL script below, you can perform the database restore process from multiple files applied previously:
使用下面的T-SQL脚本，您可以从以前应用的多个文件中执行数据库还原过程：
USE [master] RESTORE DATABASE [SQLShackDemo] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQLShackDemo.bak', DISK = N'D:\Backup\SQLShackDemo2.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 GO
Test the backup to multiple files to check how much faster you can get your backups to run and that it is easier to copy it to a network path or tape drive.
测试对多个文件的备份，以检查运行备份的速度，以及将备份复制到网络路径或磁带驱动器的过程更容易。
You can have test backup and restore scenario on a production database by using the copy-only backup option. A copy-only backup is a SQL Server backup option that is independent of the database backup sequence, which you can use to take a database backup without affecting the overall backup operation for your database.
您可以使用仅复制备份选项在生产数据库上进行测试备份和还原方案。 仅复制备份是一种SQL Server备份选项，与数据库备份顺序无关，您可以使用该备份顺序进行数据库备份，而不会影响数据库的整体备份操作。
翻译自:
转载地址：http://nsiwd.baihongyu.com/