In the past few weeks I have had to reinstall and clean up the vCloud Director 1.5 database on SQL Server 2008 R2. After a few times doing it using the SQL Server Management Studio GUI, I decided to automated it using four simple Transact-SQL scripts, so it would save me time and make it less error prone, and to better document it. I did modify the Transact-SQL part for the ALTER Database section, and I’m using a Simple Recovery mode for my database.

USE [master]

GO

CREATE DATABASE [vcloud] on PRIMARY

(NAME = N’vcloud’, FILENAME = N’D:\Microsoft SQL Server\vcloud-director\vcloud.mdf’, SIZE = 1024MB, MAXSIZE = 16384MB, FILEGROWTH = 512MB)

LOG ON

(NAME = N’vcloud_log’, FILENAME = N’D:\Microsoft SQL Server\vcloud-director\vcloud.ldf’, SIZE = 128MB, MAXSIZE = 2048MB, FILEGROWTH = 128MB)

COLLATE Latin1_General_CS_AS

GO

SQL vCD Database – Create vcloud database

USE [vcloud]

GO

ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

ALTER DATABASE [vcloud] SET MULTI_USER;

GO

USE [vcloud]

GO

ALTER DATABASE [vcloud] SET RECOVERY SIMPLE;

ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;

EXEC sp_addextendedproperty @name = N’ALLOW_SNAPSHOT_ISOLATION’, @value = ‘ON’;

ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;

EXEC sp_addextendedproperty @name = N’READ_COMMITTED_SNAPSHOT’, @value = ‘ON’;

ALTER DATABASE [vcloud] SET MULTI_USER;

GO

SQL vCD Database – Alter vcloud database

Create user vcddb

USE [vcloud]

GO

CREATE LOGIN [vcddb] WITH PASSWORD = ‘PASSWORD’, DEFAULT_DATABASE = [vcloud], DEFAULT_LANGUAGE=[us_english], CHECK_POLICY=OFF

GO

CREATE USER [vcddb] for LOGIN [vcddb]

GO

SQL vCD Database – Creat vcddba account

Modify user to add db_owner Role 

USE [vcloud]

GO

sp_addrolemember [db_owner],[vcddb]

GO