mysql2005备份_创建完整数据库备份 - SQL Server | Microsoft Docs

完整数据库备份Create a Full Database Backup

09/12/2019

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

本主题说明如何使用 SQL ServerSQL Server 、 SQL Server Management StudioSQL Server Management Studio或 PowerShell 在 Transact-SQLTransact-SQL中创建完整数据库备份。This topic describes how to create a full database backup in SQL ServerSQL Server using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

限制和局限Limitations and restrictions

不允许在显式或隐式事务中使用 BACKUP 语句。The BACKUP statement is not allowed in an explicit or implicit transaction.

无法在早期版本的 SQL ServerSQL Server 中还原较新版本的 SQL ServerSQL Server创建的备份。Backups created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.

有关备份概念和任务的概述和详细信息，请在继续操作前先参阅备份概述 (SQL Server)。For an overview of, and deeper dive into, backup concepts and tasks, see Backup Overview (SQL Server) before proceeding.

建议Recommendations

随着数据库不断增大，完整数据库备份的完成时间会延长，并且需要占用更多存储空间。As a database increases in size, full database backups take more time to complete and require more storage space. 对于大型数据库，请考虑用一系列差异数据库备份来补充完整数据库备份。For large databases, consider supplementing full database backups with a series of differential database backups.

使用 sp_spaceused 系统存储过程估计完整数据库备份的大小。Estimate the size of a full database backup by using the sp_spaceused system stored procedure.

默认情况下，每个成功的备份操作都会在 SQL ServerSQL Server 错误日志和系统事件日志中添加一个条目。By default, every successful backup operation adds an entry in the SQL ServerSQL Server error log and in the system event log. 若频繁备份，这些成功消息会迅速累积，从而生成巨大的错误日志！If you back up frequently, these success messages will accumulate quickly, resulting in huge error logs! 这会使查找其他消息变得非常困难。This can make finding other messages difficult. 在这些情况下，如果脚本均不依赖于这些备份日志条目，则可使用跟踪标志 3226 取消这些条目。In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).

SecuritySecurity

针对数据库备份，TRUSTWORTHY 设置为 OFF 。TRUSTWORTHY is set to OFF on a database backup. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET Options (Transact-SQL).

从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始，PASSWORD 和 MEDIAPASSWORD 选项不再可用于创建备份 。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), the PASSWORD and MEDIAPASSWORD options are discontinued for creating backups. 不过，您仍可以还原使用密码创建的备份。You can still restore backups created with passwords.

权限Permissions

默认情况下，为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限 。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 服务必须能够读取和写入设备，这意味着运行 SQL ServerSQL Server 服务的帐户必须对备份设备具有写入权限。The SQL ServerSQL Server service must be able to read and write to the device which means that the account under which the SQL ServerSQL Server service runs must have write permissions to the backup device. 但是，用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 因此，除非你因尝试备份或还原而访问物理资源，否则可能看不到备份设备物理文件中的这些问题。As a result, problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

备注

使用 SQL Server Management StudioSQL Server Management Studio 指定备份任务时，可以通过单击“脚本”按钮并选择脚本目标生成相应的 Transact-SQLTransact-SQL BACKUP 脚本 。When you specify a backup task by using SQL Server Management StudioSQL Server Management Studio, you can generate the corresponding Transact-SQLTransact-SQL BACKUP script by clicking the Script button and selecting a script destination.

连接到相应的 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine 实例之后，在“对象资源管理器”中，展开服务器树 。After connecting to the appropriate instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine, in Object Explorer, expand the server tree.

展开“数据库”，选择用户数据库，或展开“系统数据库”，选择系统数据库。Expand Databases, and either select a user database or expa