SQL Server Video to Rename and Move a File using PowerShell

By: Tim Smith | 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

Click Here

About the author

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"

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!