Post reply

Copy files from one directory to another using code

  • February 12, 2014 at 9:55 am

    #282414

    Hi there - I was wonder how you can copy all files with a certain file extension (.bak) from one location to another using sql code (not ssis).

    I can copy a single file using something similar to the code below;

    EXEC master..xp_cmdshell 'copy \\000.00.0.000\test.bak \\test-DB-Farm\test.bak'

    However moving forward the file name is going to change but the .bak extension isn't.

    Any help would be great!

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

    Laughing in the face of contention...

  • February 12, 2014 at 10:07 am

    #1688485

    arrjay (2/12/2014)

    Hi there - I was wonder how you can copy all files with a certain file extension (.bak) from one location to another using sql code (not ssis).

    I can copy a single file using something similar to the code below;

    EXEC master..xp_cmdshell 'copy \\000.00.0.000\test.bak \\test-DB-Farm\test.bak'

    However moving forward the file name is going to change but the .bak extension isn't.

    Any help would be great!

    If it's the DOS command you're after then it's something like this:

    copy <Source path>\*.bak <Destination path>

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

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • February 12, 2014 at 10:17 am

    #1688493

    Thanks for your response but I'm managing the whole process using sql therefore need the t-sql statement.

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

    Laughing in the face of contention...

  • February 12, 2014 at 10:26 am

    #1688496

    arrjay (2/12/2014)

    Thanks for your response but I'm managing the whole process using sql therefore need the t-sql statement.

    Sure.. so just use it in your EXEC master..xp_cmdshell

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

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • February 12, 2014 at 10:28 am

    #1688499

    Thats simple enough! Thanks!

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

    Laughing in the face of contention...

  • February 12, 2014 at 10:39 am

    #1688503

    Personally I'd suggest you use Powershell and call that from xp_cmdshell. If you move this to an agent job or elsewhere, it's cleaner, and more flexible.

    Here's a starter article: https://blogs.technet.com/b/heyscriptingguy/archive/2013/04/19/use-powershell-to-copy-files-and-folders-to-a-new-location.aspx

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • February 12, 2014 at 10:48 am

    #1688509

    Thanks Steve. The process is managed by a sql job so a powershell approach would be good. I'll have a gander at what you sent over! Thanks again.

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

    Laughing in the face of contention...

  • February 13, 2014 at 11:59 am

    #1688891

    Steve Jones - SSC Editor (2/12/2014)

    Personally I'd suggest you use Powershell and call that from xp_cmdshell.

    BWAAA-HAAA!!!! Between the two of us, we're going to give Allen White a coronary!!!!:-)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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