SQL 2008 Database Set State

Microsoft.SQLServer.2008.SetDBState (WriteActionModuleType)

This module type is used to change the state of a Microsoft SQL Server 2008 Database. It takes configuration regarding the database to alter and the state to set.

Element properties:

Member Modules:

Overrideable Parameters:

Source Code:

<WriteActionModuleType ID="Microsoft.SQLServer.2008.SetDBState" Accessibility="Internal">

  <Configuration>

    <xsd:element name="ConnectionString" type="xsd:string"/>

    <xsd:element name="Database" type="xsd:string"/>

    <xsd:element name="State" type="xsd:string"/>

    <xsd:element name="TimeoutSeconds" type="xsd:int"/>

  </Configuration>

  <OverrideableParameters>

    <OverrideableParameter ID="TimeoutSeconds" ParameterType="int" Selector="$Config/TimeoutSeconds$"/>

  </OverrideableParameters>

  <ModuleImplementation>

    <Composite>

      <MemberModules>

        <WriteAction ID="WA" TypeID="Windows!Microsoft.Windows.ScriptWriteAction">

          <ScriptName>SetSQL2008DBState.js</ScriptName>

          <Arguments>"$Config/ConnectionString$" "$Config/Database$" $Config/State$</Arguments>

          <ScriptBody><Script>

//Copyright (c) Microsoft Corporation. All rights reserved.



// Arguments (3)

// Arg 0: SQL Connection String

// Arg 1: SQL Database Name

// Arg 2: State to set to (valid SQL state e.g. Offline or Online)





var ScriptName = "Set SQL 2008 DB State";

var ADOConnectionTimeout = 15

// Failure Conidtions

var SCRIPT_ARG_FAILURE = -1;

var SCRIPT_ARG_FAILURE_MSG = "Invalid arguments passed to script";

var SCRIPT_CONNECT_FAILURE = -2;

var SCRIPT_CONNECT_FAILURE_MSG = "Could not connect to SQL";



var scriptArgs = WScript.Arguments;

if (scriptArgs.Length == 3)

{

	SetDBState(scriptArgs(0), scriptArgs(1), scriptArgs(2));

}



function SetDBState(connectionString, databaseName, databaseState)

{

	var sqlServer;

	try

	{

		sqlServer = GetADOConnection(connectionString);

	}

	catch (e)

	{

		PrintError(e);

		FailScript(e, SCRIPT_CONNECT_FAILURE_MSG);

	}

	if (sqlServer != null)

	{	

		var sqlQuery = "ALTER DATABASE [" + databaseName + "] SET " + databaseState;

		WScript.Echo("Executing Query: '" + sqlQuery + "' for instance: '" + connectionString + "'");

		sqlServer.Execute(sqlQuery);

		

	}

}



function GetADOConnection(SQLConnectionString)

{

	if (SQLConnectionString != "")

	{

		var connection = new ActiveXObject("ADODB.Connection");

		connection.Provider = "sqloledb";

		connection.ConnectionTimeout = ADOConnectionTimeout;

		connection.Open ("Server=" + SQLConnectionString + ";Database=master;Trusted_Connection=yes");

		return connection;

	}

	else

	{

		return null;

	}

		

}



function PrintError(error)

{

    WScript.Echo("============================= BEGIN SCRIPT ERROR =================================\n");

    WScript.Echo("Script Name : " + ScriptName);

    WScript.Echo("Error Type : " + error);

    WScript.Echo("Error Number : " + error.number);

    WScript.Echo("Error Code : " + (error.number &amp; 0xFFFF));

    WScript.Echo("Win32 Facility : " + (error.number&gt;&gt;16 &amp; 0x1FFF));

    WScript.Echo("Error Source : " + error.Source);

    WScript.Echo("Error Description : " + error.description + "\n");

    WScript.Echo("============================== END SCRIPT ERROR ==================================\n");

}

function FailScript(error, sMessage)

{

  var opsmgrAPI;



  opsmgrAPI = new ActiveXObject("MOM.ScriptAPI");



  opsmgrAPI.LogScriptEvent("SetSQL2008DBState.js", 4000, 1, sMessage &amp; ". " &amp; error.m_sDescription);





}



                                            </Script></ScriptBody>

          <TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>

        </WriteAction>

      </MemberModules>

      <Composition>

        <Node ID="WA"/>

      </Composition>

    </Composite>

  </ModuleImplementation>

  <InputType>System!System.BaseData</InputType>

</WriteActionModuleType>