Problem making backup of an SQL Server database

I want to make backup of an SQL Server database located on computer LAB3B-NT05 and I want the backup to be stored on another computer. Can that be done?

The commands bellow works fine if I store the database on a local disk.

exec sp_addumpdevice @.devtype = 'disk' , @.logicalname = 'Event_1' , @.physicalname = '\\kbg2863\BackupTest\EventBACKUP.bak'
GO

BACKUP DATABASE Event TO Event_1 WITH FORMAT
GO

exec sp_dropdevice @.logicalname = 'Event_1'
GO

When I execute BACKUP DATABASE Event TO Event_1 WITH FORMAT, I get the followin error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'Event_1'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

The error log says:
BackupDiskFile::CreateMedia: Backup device '\\kbg2863\BackupTest\EventBACKUP.bak' failed to create. Operating system error = 5(Access is denied.).

I have granted the user full access to '\\kbg2863\BackupTest' but still it does not work.

Any ideas?

Regards
Kjell Arne Johansen

Map a drive on the server, and reference this in yoursp_adddumpdevice.|||Thank You for your answer.

So SQL Server does not support UNC?

Regards
Kjell Arne|||

It supports but you need to make sure that the service account for SQL Server has the necessary permissions to access the network share. Generally, it is best to backup locally and then move the file.

|||

Hi all,

I want to backup folders in myserver to a tape using Windows Backup application. The problem is that I cannot see the tape drive when I tried to create the destination of the backup to it. I can see the CD-ROM, diskette drive and HDDs but I cannot see the tape drive. Why is this so? When I go to my computer windows I cannot see the tape drive, even though it is visible at the Device manager window and indicates that it is working perfectly. How do I see it?

Pls assist.

Rufus

Kjell Arne wrote:

I want to make backup of an SQL Server database located on computer LAB3B-NT05 and I want the backup to be stored on another computer. Can that be done?

The commands bellow works fine if I store the database on a local disk.

exec sp_addumpdevice @.devtype = 'disk' , @.logicalname = 'Event_1' , @.physicalname = '\\kbg2863\BackupTest\EventBACKUP.bak'
GO

BACKUP DATABASE Event TO Event_1 WITH FORMAT
GO

exec sp_dropdevice @.logicalname = 'Event_1'
GO

When I execute BACKUP DATABASE Event TO Event_1 WITH FORMAT, I get the followin error:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'Event_1'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

The error log says:
BackupDiskFile::CreateMedia: Backup device '\\kbg2863\BackupTest\EventBACKUP.bak' failed to create. Operating system error = 5(Access is denied.).

I have granted the user full access to '\\kbg2863\BackupTest' but still it does not work.

Any ideas?

Regards
Kjell Arne Johansen