Howto: Backup from remote server and store file on local drive?

  • sol.nt

    SSCrazy

    Points: 2062

    Hi everyone,

    I have granted to access and backup database of remote server on local network(LAN). I want to backup that database and store file backup on my computer driver but i don't have any option to do that, the file is created on remote computer driver.

    Is there anyway to create file on my computer driver?

    Thanks everyone

    Best regards

    Sol

  • homebrew01

    SSC Guru

    Points: 55137

    Copy it after the backup ?

  • sol.nt

    SSCrazy

    Points: 2062

    I don't have permission to access remote computer, so i can't copy to my computer

    sol

  • spaghettidba

    SSC Guru

    Points: 105661

    From BOL:

    Backing Up to a File on a Network Share

    For SQL Server to access a remote disk file, the SQL Server service account must have access to the network share. This includes having the permissions needed for backup operations to write to the network share and for restore operations to read from it. The availability of network drives and permissions depends on the context is which SQL Server service is running:

    To back up to a network drive when SQL Server is running in a domain user account, the shared drive must be mapped as a network drive in the session where SQL Server is running. If you start Sqlservr.exe from command line, SQL Server sees any network drives you have mapped in your login session.

    When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session. The session in which a service runs can have its own mapped drives, although it usually does not.

    You can connect with the network service account by using the computer account instead of a domain user. To enable backups from specific computers to a shared drive, grant access to the computer accounts. As long as the Sqlservr.exe process that is writing the backup has access, it is irrelevant whether the user sending the BACKUP command has access.

    Important:

    Backing up data over a network can be subject to network errors; therefore, we recommend that when you are using a remote disk you verify the backup operation after it finishes. For more information, see Verifying Backups.

    Specifying a Universal Naming Convention (UNC) Name

    To specify a network share in a backup or restore command, you should use the fully qualified universal naming convention (UNC) name of the file for the backup device. A UNC name has the form \\Systemname\ShareName\Path\FileName.

    For example:

    BACKUP DATABASE AdventureWorks

    TO DISK = '\\BackupSystem\BackupDisk1\AW_backups\AdventureWorksData.Bak';

    GO

  • sol.nt

    SSCrazy

    Points: 2062

    Gianluca Sartori (2/16/2009)


    From BOL:

    Backing Up to a File on a Network Share

    For SQL Server to access a remote disk file, the SQL Server service account must have access to the network share. This includes having the permissions needed for backup operations to write to the network share and for restore operations to read from it. The availability of network drives and permissions depends on the context is which SQL Server service is running:

    To back up to a network drive when SQL Server is running in a domain user account, the shared drive must be mapped as a network drive in the session where SQL Server is running. If you start Sqlservr.exe from command line, SQL Server sees any network drives you have mapped in your login session.

    When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session. The session in which a service runs can have its own mapped drives, although it usually does not.

    You can connect with the network service account by using the computer account instead of a domain user. To enable backups from specific computers to a shared drive, grant access to the computer accounts. As long as the Sqlservr.exe process that is writing the backup has access, it is irrelevant whether the user sending the BACKUP command has access.

    Important:

    Backing up data over a network can be subject to network errors; therefore, we recommend that when you are using a remote disk you verify the backup operation after it finishes. For more information, see Verifying Backups.

    Specifying a Universal Naming Convention (UNC) Name

    To specify a network share in a backup or restore command, you should use the fully qualified universal naming convention (UNC) name of the file for the backup device. A UNC name has the form \\Systemname\ShareName\Path\FileName.

    For example:

    BACKUP DATABASE AdventureWorks

    TO DISK = '\\BackupSystem\BackupDisk1\AW_backups\AdventureWorksData.Bak';

    GO

    so, if i don't have permission to access share resource, i can not backup to remote pc 🙁

    thanks Gianluca Sartori

    sol

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716619

    It's not your account that needs access to the share, it's the SQL service account.

  • Johan Bijnens

    SSC Guru

    Points: 134265

    As Steve stated, it is the sqlserver service account that needs to be granted write access to the path that you want the backup to be written to.

    If you are creating a backup using UNC to your own computer,

    be sure not to break the DRP for the db !

    Check Books Online "Backup database" and use the keyword "for copy_only"

    If I didn't have access to the db backup, I would ask one of the

    sqlserver server admins to provide me a copy of the backup file, in

    stead of taking the risk of being accused of data-theft !

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • sol.nt

    SSCrazy

    Points: 2062

    Thanks everyone,

    I guess i had my answer, what i need to do right now is go and ask admin db for backup file.

    (in my case, it is not steal, and i know my boss will not give me that file :crying: )

    thanks everyone again

    sol

  • Perry Whittle

    SSC Guru

    Points: 233794

    i would say if you dont have access to it there is possibly a very good reason

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

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • mail4nck 51599

    SSC Rookie

    Points: 45

    Hi,

    We backup our remote SQL Server to Google Drive automatically via SQLBackupAndFTP. Definitely, recommend. More details at https://sqlbackupandftp.com/blog/how-to-backup-remote-sql-server-database

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

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