Rename-Item

Question

Rename-Item

as_1234

SSCrazy

Points: 2859
More actions
September 11, 2023 at 11:52 am

#4284153

All,
I'm new to Powershell so this could be a simple error.
I'm using a SQL agent job to backup a database to a UNC path and then rename it.
The step type is 'Powershell"
The backup is done using Backup-SqlDatabase and works fine. It backups up directly to the UNC path
The rename is done as follows:
Rename-Item -Path "\\path\filename.txt" -NewName "test.bak"
I get the error "Invalid path '\\path\filename.txt'
I've used \\path\filename.txt in this post instead of the real path.
The path is valid and if I run the command from Powershell ISE it works fine.
Should it be possible to use rename-item, with a unc path, from a SQL Agent job? If so I'd appreciate any thoughts on what might be causing the error? I've checked permissions and it doesn't look to be that.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

As a random guess, does the SQL Agent Service account have access to the file you are trying to rename? If I had to guess I would say It is something with permissions on the file/folder.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

As a bit of a sidebar, renaming backup files destroys the ability to use the backup information stored in MSDB for research. If you want to name something, it should be named as a part of the BACKUP command.

And, to be honest, I see no reason at all to have to use PowerShell for anything having to do with backups or restores.

--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)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

The ONLY reason I see to use powershell for a backup process would be if you needed to move the file to a location that the SQL agent service didn't have access to, but then it would be being run outside of SQL anyway (ie windows scheduled task).

But, like Jeff, I never rename my backups and only move them from the backup location to move them to long term storage backup and that is an automated process handled by our IT team (ie not me).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeffrey Williams SSC Guru Points: 90291 More actions · Answer 4

Mr. Brian Gale wrote:

The ONLY reason I see to use powershell for a backup process would be if you needed to move the file to a location that the SQL agent service didn't have access to, but then it would be being run outside of SQL anyway (ie windows scheduled task).
But, like Jeff, I never rename my backups and only move them from the backup location to move them to long term storage backup and that is an automated process handled by our IT team (ie not me).

Additionally - I would use PowerShell to kick off Robocopy to copy the backup file(s) to a shared folder that is not on the local server. If the backup directly to the UNC path takes too long - backing up to local and copying the file can be a better option.

The only time I would use something outside SQL Server to perform backups is if the Edition does not include the agent.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael L John One Orange Chip Points: 27053 More actions · Answer 5

as_1234 wrote:

All,
I'm new to Powershell so this could be a simple error.
I'm using a SQL agent job to backup a database to a UNC path and then rename it.
The step type is 'Powershell"
The backup is done using Backup-SqlDatabase and works fine. It backups up directly to the UNC path
The rename is done as follows:
Rename-Item -Path "\\path\filename.txt" -NewName "test.bak"
I get the error "Invalid path '\\path\filename.txt'
I've used \\path\filename.txt in this post instead of the real path.
The path is valid and if I run the command from Powershell ISE it works fine.
Should it be possible to use rename-item, with a unc path, from a SQL Agent job? If so I'd appreciate any thoughts on what might be causing the error? I've checked permissions and it doesn't look to be that.
Thanks

I'm really curious about the reasons you chose to:

create a SQL Job
calls PowerShell to do a backup
Calls PowerShell to rename the file

Instead of:

Execute SQL to backup database to the filename you want

Can you tell us why you chose this method?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 6

Michael L John wrote:

as_1234 wrote:
All,
I'm new to Powershell so this could be a simple error.
I'm using a SQL agent job to backup a database to a UNC path and then rename it.
The step type is 'Powershell"
The backup is done using Backup-SqlDatabase and works fine. It backups up directly to the UNC path
The rename is done as follows:
Rename-Item -Path "\\path\filename.txt" -NewName "test.bak"
I get the error "Invalid path '\\path\filename.txt'
I've used \\path\filename.txt in this post instead of the real path.
The path is valid and if I run the command from Powershell ISE it works fine.
Should it be possible to use rename-item, with a unc path, from a SQL Agent job? If so I'd appreciate any thoughts on what might be causing the error? I've checked permissions and it doesn't look to be that.
Thanks
I'm really curious about the reasons you chose to:
create a SQL Job
calls PowerShell to do a backup
Calls PowerShell to rename the file
Instead of:
Execute SQL to backup database to the filename you want
Can you tell us why you chose this method?

He didn't answer that question when I asked it earlier in this thread, either. I don't understand why people even consider doing it with PowerShell. It's just another unneeded complexity.

I remember when PoSh first came out... It seems like everybody and their bother used PoSh to create a centralize backup system for all their servers... without realizing the absolute hell their exploding log files on all the databases on all their servers would cause if that central system couldn't get to one of the servers or if it outright failed for a period of time. 🙁

--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)

as_1234 SSCrazy Points: 2859 More actions · Answer 7

All,

Thank you for your help and apologies for not replying. Another issue has taken my attention and I haven't had chance to reply.

I will reply to everyone's questions soon, probably tomorrow.

Regarding the original issue - I think it's a permission issue that I overlooked. I will update the topic when I know.

Thanks

as_1234 SSCrazy Points: 2859 More actions · Answer 8

All,

Firstly apologies for posting a question and not replying. That wasn't my intention. Some unexpected issues took my time.

The reason for renaming is that the backup will be picked up by another process which has no access to SQL server and I want to avoid the situation where that process picks up the backup file before it completes. Therefore my plan is the following will happen:

Create backup to a temporary name

Rename to a final name

The other process is only looking for the final name

Currently Powershell backups the database to a UNC path using Backup-SqlDatabase with no problem at all. However the renane-item says that it can't find the file. The account that SQL agent uses can access the file if I logon to Windows so I'm thinking it's either:

Powershell is treating something in the path as escape characters. I've tried using the -LiteralPath parameter to avoid that

There is a security context difference between Backup-SqlDatabase and rename-item which I haven't found yet. I've tried using move-item with the same issue.

I think I've answered everyone's questions. Apologies if I missed any. I'll also post if I find the answer.

This reply was modified 12 months ago by as_1234.
This reply was modified 12 months ago by as_1234. Reason: Two typing errors corrected

Michael L John One Orange Chip Points: 27053 More actions · Answer 9

It sounds like you need to look at mirrored backups.

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/media-sets-media-families-and-backup-sets-sql-server?view=sql-server-ver16

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/