SQL Server Video to Rename and Move a File using PowerShell


By:   |   Updated: 2014-06-12   |   Comments (1)   |   Related: 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





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!

 



download





Recommended Reading

Setting the PowerShell Execution Policy

How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger

Using PowerShell to Work with Directories and Files

Bulk Copy Data from Oracle to SQL Server

Execute SQL Server Stored Procedures from PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools