Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Video to Rename and Move a File using PowerShell


By:   |   Last Updated: 2014-06-12   |   Comments (1)   |   Related Tips: More > PowerShell









Enter your business email address to
get free SQL Server tips.

Problem / Solution

A task that often needs to be performed by SQL Server DBAs and Developers is the need to move and rename files that are sitting on the file system. This may be database backup files that are moved to an archive folder along with renaming the file to include a date timestamp or maybe import or export files that are created using SSIS or T-SQL code.  There are several different ways this can be done such as using xp_cmdshell or a batch file, but in this video tip Tim Smith shows how this can be done using PowerShell.  He will also show how you can create a function that can be used over and over again by just passing in a few parameters, so that the code is reusable for any process that needs to rename and move a file.

Key Learning Items
  • Using PowerShell variables
  • PowerShell cmdlet Rename-Item
  • PowerShell cmdlet Move-Item
  • PowerShell cmdlet Get-Date
  • Creating a PowerShell function
Read Companion Tip


Last Updated: 2014-06-12


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, March 26, 2015 - 3:53:47 PM - Tim "the Trollman" Back To Top

Thank you for the video but I continue to have some errors when trying to go the other direction. I want to take the latest automatic backup file which has a filename_date and copy it from my production backup location to my reporting server location, rename, and then restore. So I need to: 1 find the latest bakup file in the folder.

2 - copy it to a new network location.

3 - rename the file.

The problem stems when run through a 2012 SQL Agent job the powershell does not like | pipe commands?

Here is the code I am using

$backuppath = "N:" -- location for the current backup file

$destpath = "C:\Databases\Backup\REPORTING\Accumed\"  -- location on the reporting server

get-childitem -path $backuppath -Filter "AUEMRDATA_Accumed_FULL_*.bak" |

     where-object { -not $_.PSIsContainer } |

     sort-object -Property $_.CreationTime |

     select-object -last 1 copy-item -Destination (join-path $destpath "AUEMRDATA_Accumed_FULL.BAK")

Any ideas would be appreciated!

 


Learn more about SQL Server tools