Udostępnij za pośrednictwem

Conditional backup of SQL Database in Release Management

  • Artykuł

The default “Backup SQL Database” in RM uses sqlcmd.exe (https://msdn.microsoft.com/en-us/library/ms162773.aspx) and the following arguments:

image

Which works fine if there is a DB to backup. Sometimes you want RM to be able to do the deployment of a clean machine with no database created, This will cause the Backup SQL Database to fail during the first deployment.

To overcome this you can create your own Action based on the sqlcmd by replacing the command:
“BACKUP DATABASE [__DatabaseName__] TO DISK = N’__Path__’”

with this sql:

“IF EXISTS (SELECT * FROM [master].dbo.sysdatabases WHERE name = '[__DatabaseName__]') BACKUP DATABASE [__DatabaseName__] TO DISK = N'__Path__' “

image

Doing a release with these configuration variables and not existing database

image

Results in status “Succeeded” and not text in the log

image

With the database created we get succeeded and the backup is performed:

image

Comments

  • Anonymous
    August 05, 2015
    Hello, Thank you for your interesting post. Just what I am looking for. After trying it out, I think there is an issue in your sql script: the comparison "name='[DatabaseName]'" should be "name='DatabaseName'" (without the [ ]). I'm running SQL Server 2014 and the original script doesn't work. Thanks again! Kind regards.