Rename-Item

  • 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

     

  • 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.

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

  • 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.

  • 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

  • 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:

    1. create a SQL Job
    2. calls PowerShell to do a backup
    3. Calls PowerShell to rename the file

    Instead of:

    1. 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/

  • 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:

    1. create a SQL Job
    2. calls PowerShell to do a backup
    3. Calls PowerShell to rename the file

    Instead of:

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

  • 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

  • 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 8 months, 3 weeks ago by  as_1234.
    • This reply was modified 8 months, 3 weeks ago by  as_1234. Reason: Two typing errors corrected
  • 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/

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

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