Can you backup sql to a network drive

The tape device must be connected physically to the computer that is running an instance of SQL Server. Backing up to remote tape devices is not supported. If a tape backup device is filled during the backup operation, but more data still must be written, SQL Server prompts for a new tape and continues the backup operation after a new tape is loaded.

You can control whether a backup tape is unloaded automatically from the tape drive after a backup or restore operation. You can control whether SQL Server keeps the tape remains open after the backup or restore operation or releases and rewinds the tape after it fills. To view a list of open tape devices and the status of mount requests, query the sys.

This view shows all the open tapes. A logical backup device is an optional, user-defined name that points to a specific physical backup device a disk file or tape drive.

A logical backup device lets you use indirection when referencing the corresponding physical backup device. Defining a logical backup device involves assigning a logical name to a physical device. The logical device name must be unique among all the logical backup devices on the server instance. To view the existing logical device names, query the sys.

This view displays the name of each logical backup device and describes the type and physical file name or path of the corresponding physical backup device.

For example, the following statement backs up the AdventureWorks database to the AdventureWorksBackups logical backup device. One advantage of using a logical backup device is that it is simpler to use than a long path. Using a logical backup device can help if you plan to write a series of backups to the same path or to a tape device. Logical backup devices are especially useful for identifying tape backup devices.

A backup script can be written to use a particular logical backup device. This lets you switch to a new physical backup devices without updating the script. Switching involves the following process:. Defining a new logical backup device that uses the original logical device name but maps to a different physical backup device.

Mirroring of backup media sets reduces the effect of backup-device malfunctions. If necessary, you can copy the backup files to a network share that is "locked down". To do this, you will need to set up a different security model. This is also documented in Permissions. Creating the backup locally When you have set up the permissions appropriately, use the Back Up wizard or Schedule Backup Jobs wizard to create a backup.

On step 3 of the Back Up wizard or step 4 of the Schedule Backup Jobs wizard: Specify a local folder for the backup location. Click Test to check whether you have the necessary permissions to write to the network share. Proceed through the rest of the wizard as normal. Backing up directly to a network share To back up directly to a network share, the account used to start up the SQL Backup Agent service application must have full permissions on the network share address in addition to the other permissions specified in Permissions.

Security and permissions The following procedure describes how to grant the permissions using Microsoft Windows 7; refer to your Microsoft Windows documentation for full details for your operating system.

In Windows Explorer, right-click on the folder in which you want to create the backups, then click Properties. On the Sharing tab, click Advanced Sharing. In the Advanced Sharing dialog, select Share this folder then click Permissions. Click OK on all of the dialog boxes. Creating the backup on a network share When you have set up the permissions appropriately, use the Back Up wizard or Schedule Backup Jobs wizard to create a backup. On step 3 of the Back Up wizard or step 4 of the Schedule Backup Jobs wizard: Specify a location on the network share as the backup location folder.

Leave the Copy backup to network box clear, unless you want to copy the backup files to a different location after they have been created on the network share. Restoring from a network share You can use SQL Backup Pro to restore a database from a backup file or set of files on a different server by either copying the files to a local folder before the restore, or restoring directly from the network location.

Edit the properties of the SQL Server service and on the Log On tab, configure the service to run as an alternate account which has network access rights. When you click OK, you will get a prompt that the settings will not take effect until the service is restarted. The services list should now show the SQL Server service is running as the account you configured. It is important to note that the backup command expects to be able to connect directly to the network share without being prompted for credentials.

The account you have configured the SQL Server service to run as must have a trusted connection with the network share where the respective credentials allow access, otherwise an error like this may occur:. Operating system error Logon failure: unknown user name or bad password. Another issue to keep in mind is the backup is performed directly to a network resource, so any hiccups in the network connection could cause your backup to fail. For this reason, you should only backup to network locations which are stable i.

As mentioned earlier, using the method where you backup locally and then copy to a network share is preferred as it allows you to run the SQL Service as an account with local system access only. By running the service as an alternate account you open the door to potential security issues. For example, a malicious SQL script could execute under the alternate account and attack network resources.

It is important to keep these points in mind if you do run your SQL Server instance using an alternate account. While these are not show stoppers if proper precautions are taken, you should consider adding additional hard drive space and then implement the local backup and copy so you can run the SQL service using a local account. Browse All iPhone Articles Browse All Mac Articles Do I need one? Browse All Android Articles Browse All Smart Home Articles Customize the Taskbar in Windows Browse All Microsoft Office Articles What Is svchost.

Browse All Privacy and Security Articles The account that you choose needs to have read and write access on the Shared Network Drive location; Best Practice would be to use the SQL Server Agent Domain Service account since this account needs write access to perform the Backups :.

I hope that everyone has an amazing day, stays focused, and makes as much progress on your goals as possible! Previous Next. Example : Testpassword!! Since this stored procedure contains a password, please do your due diligence and ensure that only the SQL Server DBA has access to view this stored procedure in the Master Database.