HowTo: Backup to UNC name using Database Maintenance Wizard

Author:

Geoff Hiten MVP

COMMUNITY SOLUTIONS CONTENT DISCLAIMER

MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

SUMMARY

SQL Server 2000 Books On-Line states that the Database Maintenance Plan Wizard only supports backups to local disks. Backups can be done to a network resource under certain circumstances.

RESOLUTION

Backups to a network folder require the following prerequisites:
1)  The SQL Server service must be running under a domain-level account.
2)  The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share.
3)  The remote share should only be accessed via UNC name.  Mapped drives may not be consistently visible to the SQL Service.
 
Once these prerequisites are met, the UNC name can be typed into the 'Use this directory' text field on the 'Complete Backup' and/or the 'Transaction Log Backup' tabs of the Database Maintenance plan.  SQL will then write backups to this location.  Do not use the '…' button.  This button will only enumerate local disk resources.

MORE INFORMATION

The most common failure cause is incorrect access permissions to the share or the underlying folder(s).  Access to the share can be tested by logging into the console of the SQL Server as the SQL Service account.  Test using the UNC share name only and not a mapped drive. 
 
Note that backing up to a network share can cause significant network traffic.  Make sure your network can handle this load without adversely affecting other systems or end users.  You may find it beneficial to establish a dedicated physical network connection between your database server and your backup file host.
गुण

आलेख ID: 555128 - पिछली समीक्षा: 14/02/2017 - संशोधन: 1

Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Standard Edition

प्रतिक्रिया