将磁盘或磁带指定为备份目标 (SQL Server)Specify a Disk or Tape As a Backup Destination (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题介绍如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中将磁盘或磁带指定为备份目标。This topic describes how to specify a disk or tape as a backup destination in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

备注

在 SQL Server 的未来版本中将不再支持磁带备份设备。Support for tape backup devices will be removed in a future version of SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

本主题内容In This Topic

开始之前Before You Begin

SecuritySecurity

权限Permissions

默认情况下,为 sysadmin 固定服务器角色以及 db_ownerdb_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 服务的帐户必须具有写入权限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice不检查文件访问权限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。Such 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

将磁盘或磁带指定为备份目标To specify a disk or tape as a backup destination

  1. 连接到相应的 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, click the server name to expand the server tree.

  2. 展开 “数据库” ,然后根据数据库的不同,选择用户数据库,或展开 “系统数据库” ,再选择系统数据库。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 右键单击数据库,指向“任务” ,再单击“备份” 。Right-click the database, point to Tasks, and then click Back Up. 将出现 “备份数据库” 对话框。The Back Up Database dialog box appears.

  4. “常规” 页的 “目标” 部分,单击 “磁盘”“磁带”In the Destination section of the General page, click Disk or Tape. 若要选择包含单个介质集的多个磁盘或磁带机(最多为 64 个)的路径,请单击 “添加”To select the paths of up to 64 disk or tape drives containing a single media set, click Add.

若要删除备份目标,请选择该备份目标并单击 “删除”To remove a backup destination, select it and click Remove. 若要查看备份目标的内容,请选择该备份目标并单击 “内容”To view the contents of a backup destination, select it and click Contents.

使用 Transact-SQLUsing Transact-SQL

将磁盘或磁带指定为备份目标To specify a disk or tape as a backup destination

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. BACKUP 语句中指定该文件或设备及其物理名称。In the BACKUP statement, specify the file or device and its physical name. 此示例会将 AdventureWorks2012 数据库备份到磁盘文件 Z:\SQLServerBackups\AdventureWorks2012.BakThis example backs up the AdventureWorks2012 database to the disk file Z:\SQLServerBackups\AdventureWorks2012.Bak.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
GO  

另请参阅See Also

备份事务日志 (SQL Server) Back Up a Transaction Log (SQL Server)
备份文件和文件组 (SQL Server) Back Up Files and Filegroups (SQL Server)
为磁盘文件定义逻辑备份设备 (SQL Server) Define a Logical Backup Device for a Disk File (SQL Server)
创建差异数据库备份 (SQL Server) Create a Differential Database Backup (SQL Server)
为磁带驱动器定义逻辑备份设备 (SQL Server)Define a Logical Backup Device for a Tape Drive (SQL Server)