Set Recovery Mode on a target database

swinghouse

Is it possible to set Recovery Mode on a database being deployed by Deployment Manager? Perhaps with a PowerShell script?

I would like to be able to automatically set our test databases in Simple Recovery Mode.

Chris.Allen

Yes, this is possible. Powershell + SQLCMD is fully supported.

I'll take a very quick look at the powershell forum to see if there's anything already there.

Chris.Allen

Couldn't find it on the forums but here's a simple example that should help:

https://community.rackspace.com/products/f/18/t/1630

swinghouse

Thank you Chris,

Got a modified version of that sample from https://community.rackspace.com/products/f/18/t/1630 working in the PostDeploy.ps1 script of a Deployment Manager project.

I didn't get the script to work with string representations of the RecoveryModel options, but had to use the enum representation like so:

[Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple

And to apply SQL Server Authentication I had to add:

#This sets the connection to mixed-mode authentication
$smo.ConnectionContext.LoginSecure=$false;

#This sets the login name
$smo.ConnectionContext.set_Login("$databaseUserName");

#This sets the password
$smo.ConnectionContext.set_Password("$databasePassword")

after instantiating the $smo object.

Thanks for the help!