Post reply

creating new backup device on network drive

  • June 25, 2004 at 9:22 am

    #111808

    Hello,

    We have several SQL servers of varying sizes and we have some issues with the current backup processes. Firstly, in a number of cases the disk space is running critically low. Secondly, the backup devices are on the same physical disk drive as the database files.

    We have been looking at investing in a NAS (Network Attached Storage) System and saving the backups directly onto this. We currently have a 30 day trial of a NAS system and I am trying to test saving backups to it (and eventually will test restoring backups from it). The problem is I don't seem to be able to create a new backup device to the NAS System (ie a network rather that local drive).

    I am a relative newcomer to the world of SQL server, but the SQL Help files inform me;

    "If the backup is to be performed over the network to a disk on a remote computer, use the universal naming convention (UNC) name in the form \\Servername\Sharename\Path\File to specify the location of the file. As with writing files to the local hard disk, the appropriate permissions needed to read or write to the file on the remote disk must be granted to the user account used by SQL Server."

    I've tried the UNC name, and the appropriate permissions have been granted....but it's still not seeing any drives other than the local c$ and d$.

    Anyone else had the same problem?

    Any help appreciated!

    Thanks

    Liz

  • June 25, 2004 at 10:01 am

    #511992

    Most of the time, it's a permission error.  Is your SQL Server service started using the Local System account or a windows user account?  To use a network drive, it needs to be started using a windows user account, with the appropriate permissions granted to this account.  To test if you have set it up correctly, use master..xp_cmdshell 'dir \\your network share'. 

    It's good practice to create a custom user account with just the basic rights on the local machine, or domain, and not to use the local / domain Administrator account.  This is because in case your SQL Server box has been compromised, the attacker will not be able to cause more damage using xp_cmdshell commands that inherits the rights of the start up service account.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • June 25, 2004 at 12:35 pm

    #512028

    quote: but it's still not seeing any drives other than the local c$ and d$.

    Those are shared drives. The drives you are attempting to access - are they shared also?

    -SQLBill

  • June 25, 2004 at 12:39 pm

    #512030

    Not related to your problem, but one thing to think of when backing up over a network.

    Who is responsible for maintaining the network? Will it be up when your backups are happening? Nothing worse than getting called in because SQL Server crashed and you find out you don't have a recent backup because the network was down for maintenance and your backup job failed.

    -SQLBill

  • June 28, 2004 at 5:24 am

    #512132

    To use UNC-Paths for a backup you have to use T-SQL, its not possible (as far as i know) to use the EnterpriseManager.

    Use BACKUP database TO DISK='\\Server\share\path\file' WITH INIT, SKIP, NAME='name', STATS = 100

    Best regards
    karl

  • June 28, 2004 at 1:04 pm

    #512256

    You can backup to network from EM, when you create the device or set the destination for the backup enter the UNC path, don't try to browse it since EM will show you only the local drives.

  • June 28, 2004 at 2:11 pm

    #512277

    I would think that Peter is on to your main problem.   The startup account for your SQL Server.  Check to make sure that the logon tab under services\mssqlserver is not set to use the "local system account".   

    Once you have your permissions correctly set another idea would be to use a mapped drive to your network share.   Since I have to manage many SQL servers, it would an ordeal deploying backup/restore scripts if each database server had different settings for a backup path.  To avoid this I use the N:\ on each db server mapped directly to the directory where the databases for that box are to be located.  Once the mapped drive is created, you can use EM to browse to the mapped drive or use TSQL to execute backup/restore without having to worry about typing or typo-ing a full UNC path for the backup/restore. 

     

  • June 29, 2004 at 2:50 am

    #512367

    Thanks for all of your replies. I seem to now have this sorted. It was a combination of a permissions problem and also not being able to "browse" the network drive in the EM maintenance plans.

    I have actually gone into the individual backup jobs that are created when you save a maintenance plan, and changed the file path strings of the xp_sqlmaint proc to point to the network drive. It does the job, but is anyone aware of any problems with this?

    Thanks,

    Liz

  • July 2, 2004 at 5:00 am

    #513111

    Peter's suggestion above is a more common/logical solution to your problem.

  • October 12, 2004 at 3:06 pm

    #526428

    We tried Dell NAS.I would not recommed it.Its very unreliable with low success ratio.

    SCSI DAS is the best.Unless you can afford FC.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply