Creating non-local backup device

Question

Post reply

Creating non-local backup device

Al Heckers

SSC Eights!

Points: 885
More actions
September 19, 2005 at 3:19 pm

#110467

Hi all. How can I create a backup device that points to another server on my network? It seems to only recoginze a local path - I've tried \\servername\sql backup but it returns an error that it cannot open the backup device. Thanks.
-Al

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

From Books Online

Backup Devices

When creating backups, you must select a backup device for the data to be backed up to. Microsoft® SQL Server™ 2000 can back up databases, transaction logs, and files to disk and tape devices.

Disk Devices

Disk backup devices are files on hard disks or other disk storage media and are the same as regular operating system files. Referring to a disk backup device is the same as referring to any other operating system file. Disk backup devices can be defined on a local disk of a server or on a remote disk on a shared network resource, and they can be as large or as small as needed. The maximum file size is equivalent to the free disk space available on the disk.

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.

Al Heckers SSC Eights! Points: 885 More actions · Answer 2

OK, thanks. To what account do I give share permissions?

-Al

Al Heckers SSC Eights! Points: 885 More actions · Answer 3

Or maybe the better question is: How do I find out to what account do I give share permissions?

Thomas Gehrke Grasshopper Points: 19 More actions · Answer 4

I believe the service startup account for the SQL Server Agent needs to have Full access to both the share and the file system.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Both the SQL Server and SQL Agent startup accounts need to be domain user accounts with the proper 'rights'. I state this becaause I do not know what user context you are going to be using.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

vambati SSCrazy Points: 2769 More actions · Answer 6

U can test the permissions by trying to map the other server drive to the parent server where u are creating the backup device.

Cheers,

Babu.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Here's a quick SQL script to execute for checking your permissions for creation, listing and deletion of files:

declare @cmd varchar(128)

select @cmd = 'echo test_string > \\your-server-name\i$\test_file.dat'

exec master..xp_cmdshell @cmd

--

-- output should look similar to:

--

-- output

-- --------------------------------

-- NULL

--

-- (1 row(s) affected)

--

select @cmd = 'dir \\your-server-name\i$\test_file.dat'

exec master..xp_cmdshell @cmd

--

-- output should look similar to:

--

-- output

-- ----------------------------------------------------------------

-- Volume in drive \\your-server-name\i$ is SQL Backups

-- Volume Serial Number is 5CA4-3C74

-- NULL

-- Directory of \\your-server-name\i$

-- NULL

-- 09/22/2005 12:57 PM 14 test_file.dat

-- 1 File(s) 14 bytes

-- 0 Dir(s) 224,460,341,248 bytes free

-- NULL

--

-- (9 row(s) affected)

--

select @cmd = 'del \\your-server-name\i$\test_file.dat'

exec master..xp_cmdshell @cmd

--

-- output should look similar to:

--

-- output

-- ----------------------------------------------------------------

-- NULL

--

-- (1 row(s) affected)

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."