Problem making backup of an SQL Server database

Hot Topic

Converting a parent-child table into a genrational table / text file
SELECT permission denied on object 'database object', database 'databasename', owner 'dbo'.
Transfering and verifying data using temp tables
Gantt chart in SQL reporting service
How to connect database under Windows Authentication?
SQL Express 2005 RTM?
AdventureWorks database not seen by Management Studio Express CTP
How to aggregate a column that was itself an aggregate
cannot start or verify that SQL 2005 is running
SP simplification for performance

SQL Server

MDX script to scope on all but the leaf levels
Database Transfer Error
Two Y axis Display
Msg 3616 Transaction doomed, Triggers and Error logging
Change data source dynamically in report server.
Using UDL insde SSIS package
Convert SQLExpress db to Sql Anywhere
Dynamic YTD based on user selection on Month
How do I attach query results to send mail
Service Broker Security Question

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


 




Answer this question

Problem making backup of an SQL Server database

  • 10kap

    Thank You for your answer.

    So SQL Server does not support UNC

    Regards
    Kjell Arne

  • Rober

    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.


  • Tom Sharpless

    Map a drive on the server, and reference this in yoursp_adddumpdevice.


  • kspreston

    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




  • Problem making backup of an SQL Server database
Answer this question