Post reply

BACKUP databases

  • November 6, 2009 at 7:46 pm

    #385731

    I would like to backup certain databases using jobs and then copy them back to a server server001. This involves T-SQL and I have accomplished the first part for backing up, but the second one (I am doing manually) which has DOS command like copy / move as key player acts like road block. Any suggestions ?:angry:

  • November 6, 2009 at 9:10 pm

    #1076219

    If enabled, you could use xp_cmdshell.

    Another alternative would be to write a batch script and have that called from a Windows scheduled task.

    There are myriad ways to achieve this - which one is right for you depends on your circumstances.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 7, 2009 at 4:41 pm

    #1076385

    No xp_cmdshell allowed in my circumstances.

    Could you provide sample code(s) for other feasible and efficient ways.

    Thanks.

  • November 7, 2009 at 9:56 pm

    #1076414

    Sorry if I was less than clear: there are many ways to achieve want you want, so it would help a great deal if you could explain more about the context, and what things are important to you in the solution. More details = better answers 🙂

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 8, 2009 at 3:29 pm

    #1076495

    I need to backup a database (dbAA to \\server001\DBbackups\dbAA.BAK) on server001 and copy dbAA.BAK to \\server002\DBbackups\dbAA.BAK.

    First action needs a "BACKUP DATABASE ....." ans second needs a DOS action "COPY \\server001\DBbackups\dbAA.BAK to \\server002\DBbackups\dbAA.BAK".

    Hopefully the above is clear!

    Thanks.

  • November 9, 2009 at 6:04 am

    #1076618

    I do this with most of my backups. The first step of the sql agent job does the backup then if success goes to a next step which is set as an an Operating System (CMDEXEC) step and with Run As set to an administrator account. The job step command is then simply

    Copy "c:\backups\db.bk" "\\Secondservername\c:\copiesdir\copybackup.bak" etc. Quotes are needed.

    If you can't set it up yourself (not a sysadmin) get your DBA to do it.

    And remember that the Run As account needs write permissions on the destination and read permissions on the source directories.

  • November 9, 2009 at 8:25 am

    #1076663

    Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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