1

For my company I have developed a small .bat script with which I backup a Sqlserver database and perform some operations on the generated backup files. Currently the script works correctly, but I need to add the date to the backup file name. For example now I have Bck_DBTest file and I would like Bck_DBTest_yyyy_mm_dd I have tried various methods but have never been able to append the date to the file name.

Do you have any tips or advice on how to achieve this?

Thanks in advance

  • MY_CODE:
powershell -Command "Remove-item -Path C:\Backup\db_temp\Backup_TmpTest.bak -Force"
powershell -Command "Remove-item -Path C:\Backup\db_prod\Backup_Tmp.bak -Force"
set date = powershell -Command "Get-Date -Format MM-dd-yyyy"
sqlcmd -S WIN-xxxxxxxxx -i C:\ScriptBackup\DBBck.sql -o executeBackupOut.txt -U usersql -P usersql
powershell -Command "Compress-Archive -Path C:\Backup\db_temp\Backup_TmpTest.bak -Force -DestinationPath C:\Backup\db_temp\Backup_TmpTest_@date.zip"
powershell -Command "Compress-Archive -Path C:\Backup\db_prod\Backup_Tmp.bak -Force -DestinationPath C:\Backup\db_prod\Backup_Tmp_@date.zip"
EXIT
64Bit1990
  • 302
  • 2
  • 16

2 Answers2

0

set date = powershell -Command "Get-Date -Format MM-dd-yyyy"

  • Setting variable a variable date is best avoided, because it is dynamic variable maintained by cmd.exe

  • Leaving that aside, in cmd.exe SET statements, do not surround = with whitespace, because the whitespace will become part of the variable name and/or the value.

  • You cannot directly assign the output from commands to cmd.exe variables that way - for that you need a for /f statement.

C:\Backup\db_temp\Backup_TmpTest_@date.zip

Assuming that variable date was defined as intended, there are two ways to refer to a variable defined with SET in cmd.exe in a call to powershell.exe, the Windows PowerShell CLI:

  • Either: Rely on cmd.exe's up-front string interpolation and embed %date% in the command line:

    powershell -c "Compress-Archive ... C:\...\Backup_TmpTest_%date%.zip"

  • Or: More robustly, in -Command (-c) based calls, rely on the fact that SET-defined variables in cmd.exe are seen as environment variables in PowerShell, use $env:date (this does not work for dynamic cmd.exe variables, however):

    powershell -c "Compress-Archive ... C:\...\Backup_TmpTest_$env:date.zip"

Given the cost of individual PowerShell CLI calls (as discussed below), you can simply nest PowerShell commands in a single call:

powershell -c "Compress-Archive ... C:\...\Backup_TmpTest_$(Get-Date -Format MM-dd-yyyy).zip"

Taking a step back:

Every PowerShell CLI call is:

  • computationally expensive

  • no state is maintained between calls.

Therefore:

  • Either: consolidate your multiple calls into a single call

    • This can hurt readability, but there are ways - which require careful escaping - to spread such a multi-statement call across multiple lines - see this answer.

  • Or: Outsource your multiple PowerShell statements to a script file (*.ps1), which you can call with powershell.exe -File.

mklement0
  • 382,024
  • 64
  • 607
  • 775
0

Or: Outsource your multiple PowerShell statements to a script file (*.ps1), which you can call with powershell.exe -File.

mklement0's statement implies the need for 2 files, the BATCH file and the PowerShell script. But there is also a method for combining both into a Polyglot.

Place the following code in a file with the CMD extension and, when ran, it will first execute as a BATCH file (running the section between the <# and #>), and BATCH will in turn call PowerShell which in turn will load and run the file itself as a PowerShell script block.

NOTE:

  • Most of the PowerShell commands in your example script are included in this script, but are surrounded by double quotes ", so instead of executing, they simply display what would have been executed.
  • Also, the back tick ` was used to escape the preexisting double quotes in the last 2 lines and a slight modification was made to make the $Date variable work in the strings.
  • This means you will need to remove the extra double quotes and back ticks to reactivate these lines.
<# :  REM [### https://stackoverflow.com/questions/43882863/strange-redirect-in-batch-powershell-polyglot ###]
@ECHO OFF
    REM [### Ensure extensions enabled ###]
    SETLOCAL ENABLEEXTENSIONS
    REM [### Define environment variable f0 as this file ###]
    SET f0=%~f0
    REM [### Run this file as a PowerShell script block with $PSScriptRoot and $PSCommandPath defined: https://stackoverflow.com/a/67517934/4190564 ###]
    PowerShell -NoProfile -ExecutionPolicy RemoteSigned -Command ". ([System.Management.Automation.Language.Parser]::ParseInput((get-content -raw $Env:f0), $Env:f0, [ref]$null, [ref]$null)).GetScriptBlock()"
GOTO :EOF
#>
"Remove-item -Path C:\Backup\db_temp\Backup_TmpTest.bak -Force"
"Remove-item -Path C:\Backup\db_prod\Backup_Tmp.bak -Force"
$Date = Get-Date -Format MM-dd-yyyy
"sqlcmd -S WIN-xxxxxxxxx -i C:\ScriptBackup\DBBck.sql -o executeBackupOut.txt -U usersql -P usersql"
"Compress-Archive -Path C:\Backup\db_temp\Backup_TmpTest.bak -Force -DestinationPath `"C:\Backup\db_temp\Backup_TmpTest_$Date.zip`""
"Compress-Archive -Path C:\Backup\db_prod\Backup_Tmp.bak -Force -DestinationPath `"C:\Backup\db_prod\Backup_Tmp_$Date.zip`""

The results are the following lines of text, which represent what would have been executed if the extra double quotes and back ticks are removed:

Remove-item -Path C:\Backup\db_temp\Backup_TmpTest.bak -Force
Remove-item -Path C:\Backup\db_prod\Backup_Tmp.bak -Force
sqlcmd -S WIN-xxxxxxxxx -i C:\ScriptBackup\DBBck.sql -o executeBackupOut.txt -U usersql -P usersql
Compress-Archive -Path C:\Backup\db_temp\Backup_TmpTest.bak -Force -DestinationPath "C:\Backup\db_temp\Backup_TmpTest_06-08-2023.zip"
Compress-Archive -Path C:\Backup\db_prod\Backup_Tmp.bak -Force -DestinationPath "C:\Backup\db_prod\Backup_Tmp_06-08-2023.zip"
Darin
  • 1,423
  • 1
  • 10
  • 12