Set Recovery Mode on a target database

swinghouseswinghouse Posts: 120 Bronze 2
edited April 20, 2014 4:20PM in Deployment Manager
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.

Comments

  • 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.
  • 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
  • swinghouseswinghouse Posts: 120 Bronze 2
    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!
Sign In or Register to comment.