SQL2005 database creation script

Question

SQL2005 database creation script

jrmtl

Ten Centuries

Points: 1075
More actions
June 8, 2009 at 12:59 pm

#134845

Hello all,
I know this may sound basic but I was unable to find a database creation script in the scripts section ?
Can someone point me to where I can find ?
Much appreciated

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's a lot of ways to do this;

the most basic is simply 3 words: CREATE DATABASE [WHATEVER], where whatever is the database name. when you do that, a copy of the model database is created and renamed to the name you specify.

now in SSMS, if you right click on a database name in the Object Explorer panel, you can select Script database, and ti will add a lot more information as far as database options:

USE [master]

GO

/****** Object: Database [Sandbox] Script Date: 06/08/2009 15:08:05 ******/

CREATE DATABASE [Sandbox] ON PRIMARY

( NAME = N'Sandbox', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Sandbox.mdf' , SIZE = 81920KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Sandbox_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Sandbox_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'Sandbox', @new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Sandbox].[dbo].[sp_fulltext_database] @action = 'disable'

end

GO

ALTER DATABASE [Sandbox] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [Sandbox] SET ANSI_NULLS OFF

GO

ALTER DATABASE [Sandbox] SET ANSI_PADDING OFF

GO

ALTER DATABASE [Sandbox] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [Sandbox] SET ARITHABORT OFF

GO

ALTER DATABASE [Sandbox] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [Sandbox] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [Sandbox] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [Sandbox] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [Sandbox] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [Sandbox] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [Sandbox] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [Sandbox] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [Sandbox] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [Sandbox] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [Sandbox] SET ENABLE_BROKER

GO

ALTER DATABASE [Sandbox] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [Sandbox] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [Sandbox] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [Sandbox] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [Sandbox] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [Sandbox] SET READ_WRITE

GO

ALTER DATABASE [Sandbox] SET RECOVERY FULL

GO

ALTER DATABASE [Sandbox] SET MULTI_USER

GO

ALTER DATABASE [Sandbox] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [Sandbox] SET DB_CHAINING OFF

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jrmtl Ten Centuries Points: 1075 More actions · Answer 2

Thanks for the quick response... i haven't touched sql server in a while and just getting back into it 🙂

I am installing a brand new server and a base database for an application