SQLPackage.exe filename with the date

Question

I am trying to automate a backup of an Azure database to my local machine using SQLPackage.exe. I am trying to add the date onto the filename so that every night it doesn't get overwritten. The following line will pick up the date but will then stop the backup running with the error shown below

CMD

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /Action:Export /ssn:SERVER_NAME_HERE /sdn:DATABASE_NAME /su:USERNAME /sp:PASSWORD /tf:C:\Users\William\Desktop\BackupTest\BACKUPFILE'%date%'.bacpac

ERROR

*** Unrecognized command line argument '23/06/2017'.bacpac'.

I have tried using

+%date%+
+%date

And other options but no luck. Can anyone suggest anything?

Why are you trying to escape it at all? Just plain `%date%` should work unless it complains about the slashes. If that's the case you can break it up like here https://stackoverflow.com/questions/1192476/format-date-and-time-in-a-windows-batch-script — kichik, Jun 23 '17 at 00:01
Thanks for the link. I ended up using the format %DATE:~10,4%_%DATE:~7,2%_%DATE:~4,2% which gave the me 2017_06_23 — WillNZ, Jun 23 '17 at 00:13

score 1 · Answer 1 · edited Nov 11 '18 at 03:44

You can add it using PowerShell as explained on below example.

Param(
    [Parameter(Position=0,Mandatory=$true)]
    [string]$ServerName
)

cls

try {
    if((Get-PSSnapin -Name SQlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null){
        Add-PSSnapin SQlServerCmdletSnapin100
    }
}
catch {
    Write-Error "This script requires the SQLServerCmdletSnapIn100 snapin"
    exit
}

$script_path = Split-Path -Parent $MyInvocation.MyCommand.Definition

$sql = "
    SELECT name
    FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb','distribution')
"

$data = Invoke-sqlcmd -Query $sql -ServerInstance $ServerName -Database master

$data | ForEach-Object {


$DatabaseName = $_.name
$now=get-Date

#
# Run sqlpackage
#
&"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" `
    /Action:extract `
    /SourceServerName:$ServerName `
    /SourceDatabaseName:$DatabaseName `
    /TargetFile:$script_path\DACPACs\$DatabaseName$now.dacpac `
    /p:ExtractReferencedServerScopedElements=False `
    /p:IgnorePermissions=False


}

Hope this helps.

Regards,

Alberto Morillo

SQLCoffee.com

score 1 · Answer 2 · answered Jun 23 '17 at 02:46

More fundamentally, it is not recommend using bacpac to backup database. Bacpac is for load & move data in and out of Azure on demand.

SQLDB on Azure has backup service on by default so a scheduled backup is already provided by the service.

In addition, to properly make a bacpac, the database needs to be copied first then make a bacpac from the copy. Otherwise transactional consistency is not guaranteed and importing the bacpac can fail in the worst case.

SQLPackage.exe filename with the date

2 Answers2