creating new backup device on network drive

Question

Post reply

creating new backup device on network drive

Elisabeth Turner

SSC Enthusiast

Points: 197
More actions
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

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

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

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

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.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

SQLBill

SSC Guru

Points: 51440

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

SQLBill SSC Guru Points: 51440 More actions · Answer 3

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

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 4

Karl Klingler

SSCertifiable

Points: 5869

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

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 5

mdaniel

SSCarpal Tunnel

Points: 4616

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.

mpinnow SSChasing Mays Points: 602 More actions · Answer 6

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.

Elisabeth Turner SSC Enthusiast Points: 197 More actions · Answer 7

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