A disk backup device is a hard disk or other disk storage media that contains one or more backup files. A backup file is a regular operating system file.
If a disk file fills while a backup operation is appending a backup to the media set, the backup operation fails. The maximum size of a backup file is determined by the free disk space available on the disk device; therefore, the appropriate size for a backup disk device depends on the size of your backups.
A disk backup device could be a simple disk device, such as an ATA drive. Alternatively, you could use a hot-swappable disk drive that would let you transparently replace a full disk on the drive with an empty disk. A backup disk can be a local disk on the server or a remote disk that is a shared network resource. For information about how to use a remote disk, see "Backing Up to a File on a Network Share," later in this topic.
SQL Server management tools are very flexible at handling disk backup devices because they automatically generate a time-stamped name on the disk file.
.gif) Important: |
|---|
|
We recommend that a backup disk be a different disk than the database data and log disks. This is necessary to make sure that you can access the backups if the data or log disk fails.
|
Specifying a Backup File by Using Its Physical Name
The basic BACKUP syntax for specifying a backup file by using its physical device name is:
BACKUP DATABASE database_name
TO DISK = { 'physical_backup_device_name' | @physical_backup_device_name_var }
For example:
BACKUP DATABASE AdventureWorks
TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak';
GO
To specify a physical disk device in a RESTORE statement, the basic syntax is:
RESTORE { DATABASE | LOG } database_name
FROM DISK = { 'physical_backup_device_name' | @physical_backup_device_name_var }
For example,
RESTORE DATABASE AdventureWorks
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak';
Specifying the Path of a Disk Backup File
When you are specifying a backup file, you should enter its full path and file name. If you specify only the file name or a relative path when you are backing up to a file, the backup file is put in the default backup directory. The default backup directory is C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Backup, where n is the number of the server instance. Therefore, for the default server instance, the default backup directory is: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup.
To avoid ambiguity, especially in scripts, we recommend that you explicitly specify the path of the backup directory in every DISK clause. However, this is less important when you are using Query Editor. In that case, if you are sure that the backup file resides in the default backup directory, you can omit the path from a DISK clause. For example, the following BACKUP statement backs up the AdventureWorks database to the AdventureWorks.bak backup file in the default backup directory.
BACKUP DATABASE AdventureWorks
TO DISK = ’AdventureWorks.bak’;
GO
| Note: |
|---|
|
The default location is stored in the BackupDirectory registry key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer.
|
Backing Up to a File on a Network Share
For SQL Server to access a remote disk file, the SQL Server service account must have access to the network share. This includes having the permissions needed for backup operations to write to the network share and for restore operations to read from it. The availability of network drives and permissions depends on the context is which SQL Server service is running:
-
To back up to a network drive when SQL Server is running in a domain user account, the shared drive must be mapped as a network drive in the session where SQL Server is running. If you start Sqlservr.exe from command line, SQL Server sees any network drives you have mapped in your login session.
-
When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session. The session in which a service runs can have its own mapped drives, although it usually does not.
-
You can connect with the network service account by using the computer account instead of a domain user. To enable backups from specific computers to a shared drive, grant access to the computer accounts. As long as the Sqlservr.exe process that is writing the backup has access, it is irrelevant whether the user sending the BACKUP command has access.
| Important: |
|---|
|
Backing up data over a network can be subject to network errors; therefore, we recommend that when you are using a remote disk you verify the backup operation after it finishes. For more information, see Verifying Backups.
|
Specifying a Universal Naming Convention (UNC) Name
To specify a network share in a backup or restore command, you should use the fully qualified universal naming convention (UNC) name of the file for the backup device. A UNC name has the form \\Systemname\ShareName\Path\FileName.
For example:
BACKUP DATABASE AdventureWorks
TO DISK = '\\BackupSystem\BackupDisk1\AW_backups\AdventureWorksData.Bak';
GO