SQL Server Video to Rename and Move a File using PowerShell

By:   |   Comments (1)   |   Related: > PowerShell

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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!


get free sql tips
agree to terms