[FNS-15] Creating and configuring your SQL Server database using a script instead of Server Management Studio


Description

Creating and configuring your SQL Server database using a script instead of Server Management Studio

If you are unable to create your database using Microsoft SQL Server Management Studio as explained in our quick start guide, it is possible to use a script to replicate the process. Simply use search & replace to configure the script below with the values you would like to use.

Ensure you have sufficient permissions to create and edit users and databases.

/*
 * This script will create a new database with the correct configuration for FusionAnalytics 
 * as well as a user as the database owner.
 *
 * # Replace all occurences of "yourDatabaseName" with the name you wish to use. There cannot be an existing database with this name.
 * # Replace all occurences of "yourDirectory" with the directory you with to use without a trailing slash. This must already exist.
 *
 * # Replace "yourUsername" with the username you wish to use
 * # Replace "yourPassword" with the password you wish to use
 *
 * # Set the maximum server memory near the bottom of the script
 *
 */ 


USE Master
GO

--Create a new user
CREATE LOGIN [yourUsername] WITH PASSWORD=N'yourPassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[English], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

--Create the database. The database must not already exist.
CREATE DATABASE yourDatabaseName ON PRIMARY
( NAME = 'yourDatabaseName',
  FILENAME = 'yourDirectory\yourDatabaseName.mdf',
  SIZE = 25mb,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 10% )
  
--Create log file
LOG ON
( NAME = 'yourDatabaseName_log',
  FILENAME = 'yourDirectory\yourDatabaseNamelog.ldf',
  SIZE = 7mb,
  FILEGROWTH = 10%  )
GO

USE yourDatabaseName
GO

--Set recommended parameters
EXEC sp_dboption N'yourDatabaseName', N'autoshrink', N'true'
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)', 4000 --***SET THIS NUMBER TO YOUR CHOSEN MEMORY LIMIT (MB)***
RECONFIGURE WITH OVERRIDE

ALTER DATABASE yourDatabaseName SET RECOVERY SIMPLE

-- Set the db owner
USE yourDatabaseName
EXEC sp_changedbowner 'yourUsername'

Issue Details

Type: Technote
Issue Number: FNS-15
Components: Setup
Environment:
Resolution: Fixed
Added: 30/09/2011 10:53:03
Affects Version:
Fixed Version: 1.0.0
Server:
Platform:
Related Issues: None