Backup and save a copy on a server on a different domain

  • Hi

    I would like to set up a daily job that backs up the database and transfers a copy of the backup on to a disk on a different domain. I wonder how to pass the authentication when connecting to a different domain. Thank you in advance.:-)

    M

    Where there is a will... There is a way!

  • Junior DBA.M (4/17/2012)


    Hi

    I would like to set up a daily job that backs up the database and transfers a copy of the backup on to a disk on a different domain. I wonder how to pass the authentication when connecting to a different domain. Thank you in advance.:-)

    M

    You can either use pass through authentication which requires a matching local username and password on each server or if you have the appropriate trusts in place you may use domain accounts.

    Can you provide a little more detail as to what your current situation provides

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi and thanks for your reply

    We have two servers on a work group. The second server does not have SQL server on it, it is just a disk. It is Windows server 2003...

    Where there is a will... There is a way!

  • The second server has no SQL Server installed?

    So is you question about attaching to database files located on a different domain?

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • Hi

    The purpose of this job would be only to save a copy of the backup on the second server. There is no need to attach files or anything, its just to have a copy on a disk in the work group with a different domain, and I would like to know how to pass the authentication. Thanks.

    Where there is a will... There is a way!

  • i would look at something like robocopy or xcopy running as batch file as a second step in the backup job which copies the files from server1 to server2

  • Set up a shared directory on the second server, with "Full" permissions for an account on both domains. Then robocopy the files over.

    If you can't do that, can you set up FTP on the second server and upload to that? Can be set for internal connections only. I've had to do that with remote servers that had strict domain account rules.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Junior DBA.M (4/17/2012)


    Hi and thanks for your reply

    We have two servers on a work group. The second server does not have SQL server on it, it is just a disk. It is Windows server 2003...

    As both machines are in a workgroup you may use passthrough authentication, create a local user on each box with exactly the same password.

    If you want the sql server instance to copy the files out to the remote server you will need to run the sql server service under this local user account, otherwise use a scheduled windows task to copy the files for you.

    For more on pass through and moving files remotely in a workgroup or cross domains see my guide at this[/url] link. The guide mentions Log Shipping but the principals are the same.

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thank you. Will try and let you know if I was succesful .:-)

    Where there is a will... There is a way!

  • Hiiiii

    thank you very much. I tested this and worked. :w00t::-D:-):hehe: As you can see, I am an excited newbie!

    Regards

    Where there is a will... There is a way!

  • You're welcome

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi

    I was able to copy the backup file to a disk on a different domain, now due to disk space issue, I need to backup straight to the disk on a different domain.

    I created a new job Of type transact Sql, with command:

    Backup database master to Disk= '\\IPaddress\share\master.bak'

    I am using passthrough authentication, but I get this error (Can not open backup device.... Operating system error 5.

    Is it the SQL server account that needs acces to the share folder or is it becasue the job is written wrong.

    Please help. Thank you.

    M

    Where there is a will... There is a way!

  • I'm assuming this is a new share you have created for the backups is it?

    \\IPaddress\share

    The backup will run under the sql server agent, is this service using a pass through account with sufficient permission on the remote share?

    Verify by checking the share permissions on the sharing tab and the NTFS permissions on the security tab. Where the 2 combine, the most restrictive applies and on a new share the default share permission is Everyone - ReadOnly 😉

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • So I made sure the permissions are correct, This is a new share that SQL agent account has read and write access to. Both the servers are in one work group, but they are on different domains. I can backup and copy accross the backup, but I can not backup the database straight on the second disk, The error I get is:

    Can not open backup device... Operating system error 5...

    Any help would be really appriciated. Thank you...

    Where there is a will... There is a way!

  • Found the Answer to my last question...

    Here is the link

    http://blogs.msdn.com/b/varund/archive/2009/06/04/backup-sql-server-database-to-a-share-drive-on-another-server.aspx

    I just mapped to the drive, and now SQL Server can see it, and can backup to it!!

    😉

    Where there is a will... There is a way!

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

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