Post reply

SSIS package to transfer database/backup to server in other domain

  • July 1, 2008 at 2:06 am

    #129805

    Hi !

    I am totally new to SSIS. Need to configure a task to copy database / database back upto other server.

    Please suggest.

    I tried to do it using Transfer DB task , but the connection to destination server ( diffrent domain) failed.

    Please advise.

    Thanks !

  • July 1, 2008 at 2:19 am

    #836496

    Does the login which you use for performing this operation, exist in the destination server ?

    [font="Verdana"]- Deepak[/font]

  • July 1, 2008 at 3:09 am

    #836517

    Yes , It does.

  • July 1, 2008 at 3:59 am

    #836529

    Or Else We can use back up task followed by FTP tasks to send/Recieve compressed back up files .

    What is the cmdshell syntax to compress backup files ?

  • July 1, 2008 at 5:50 am

    #836576

    [font="Verdana"]Hi,

    Try the steps below

    *) Provide modify privilege to SQL Service account on the share

    *) Take backup to the local drive -- CREATE maint plan to take the backup, schedule this as a job

    *) Copy the backup FTP or DOS command -- Make this is step two to copy the current backup to the share drive.

    [/font]

    Regards..Vidhya Sagar
    SQL-Articles

  • July 1, 2008 at 7:24 am

    #836663

    you can copy entire database contents by using export wizard.

    Choose destination db to which data is copied and run the wizard.

    U can also save package as SSIS and select option rely on sql server and u can schedule the as job by select in steps the SSIS package.

    Try it.

    Any comments pls.:D

  • July 1, 2008 at 10:24 pm

    #836983

    anjan.ashok (7/1/2008)

    you can copy entire database contents by using export wizard.

    Choose destination db to which data is copied and run the wizard.

    U can also save package as SSIS and select option rely on sql server and u can schedule the as job by select in steps the SSIS package.

    Try it.

    Any comments pls.:D

    I know how to copy db using export wizard but could you please explain how I can save it as SSIS package?

  • July 1, 2008 at 10:39 pm

    #836986

    [font="Verdana"]Hi,

    In the meantime, you can try Copy database wizard for the same.

    How to use Copy Database Wizard[/url][/font]

    Regards..Vidhya Sagar
    SQL-Articles

  • July 1, 2008 at 11:06 pm

    #836998

    Thanks for all your inputs !

    This copying of back up and then restore on the other server is being carried out manually as a daily activity. Export / Copy database may not help because the servers are in different domains and do not share any common logins.

    An FTP task ( SSIS ) would be suitable as per requirement. Can anyone guide me to use FTP task, including enabling FTP on server . Also pls advise on how to compress the backup file, using t-sql commands ( xp_cmdshell).

    Thanks !!

  • July 3, 2008 at 8:11 am

    #837879

    I do this process of copying database over to other server using a nightly backup and restore job. Backup from one server using scheduled job is written direct to other server using a network share. Then a restore job is run on other server. This has been running sucessfully for last 3 years.:)

  • July 10, 2008 at 7:24 am

    #840797

    Use the attached action plan.

  • July 12, 2008 at 1:41 am

    #841954

    mani.balaji (7/10/2008)

    Use the attached action plan.

    Thanks a million mani.balaji:)

  • July 16, 2008 at 11:16 am

    #843960

    Hey Naresh,

    I'm trying to achieve something similar. Can you please guide me in steps I need to take to achieve this. (Including how to define this share)

    FYI, The same login works on both the servers.

    I have Prod1 Server & a Prod2 Server.

    I need to Backup the database db1 from Prod1 to db1 in Prod2.

    any help will be appreciated.

  • July 17, 2008 at 3:21 pm

    #844691

    The account running sql agent on prod1 server should have a write permission on the shared folder on the second server.

    Create a job which runs backup and write to other server on the share.

    create another job on the second server which runs restore of the db.

    Hope it helps.

    Naresh

  • July 16, 2009 at 5:44 am

    #1025543

    Hi all,

    I had a database backup database1.bak in one server and i need to restore it to some other database in a different server.

    I dont want to copy the database backup files between the servers.

    Can any one suggest a method (using TSQL or SSIS package).

    Thanks in advance.

    pvsrpk

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

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