Creating a SQL Server database by script

This is easy enough and you can find scripts to create databases in SQL Server on the Microsoft technet and MSDN sites. I include it here to show you my preference. If you want to play with this do a search and replace to change the string ‘yourdb’ with whatever name you prefer. Be sure you don’t already have a db with the same name.  Also remember to change the hyphens to – - for the comments!

use master;
go

– if yourdb exists already delete it
if db_id (N’yourdb’) is not null drop database yourdb;
go

– Create the db, you may need to change the filename path
– if you installed SQL Server without the defaults
– The sizes here are up to you; I chose these for performance reasons
– but if your planned use for the db requires more oomph, add more yourself
create database yourdb
on
( name = yourdb_dat,
filename = ‘c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf’,
size = 25mb,
maxsize = 1500mb,
filegrowth = 10mb )
log on
( name = ‘yourdb_log’,
filename = ‘c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf’,
size = 7mb,
maxsize = 375mb,
filegrowth = 10mb )
COLLATE SQL_Latin1_General_CP1_CI_AS;
go

– verify the database files and sizes
select name, physical_name, size, size*1.0/128 as [size in mbs]
from sys.master_files
where physical_name like N’%yourdbdat.mdf’ or
physical_name like N’%yourdblog.ldf’;
go

use yourdb
go

exec sp_dboption N’yourdb’, N’autoshrink’, N’false’
GO
exec sp_dboption N’yourdb’, N’autoclose’, N’false’
GO
exec sp_dboption N’yourdb’, N’quoted identifier’, N’false’
GO
exec sp_dboption N’yourdb’, N’ANSI warnings’, N’false’
GO
exec sp_dboption N’yourdb’, N’auto create statistics’, N’true’
GO
exec sp_dboption N’yourdb’, N’auto update statistics’, N’true’
GO

set nocount on
– A build table is useful if you develop a lot of databases,
– when you make changes you can always insert another
– row and have a personalized audit trail
create table [dbo].[tblBuild] (
[strscripttype] [nvarchar] (60) not null constraint def_tblBuild_strscripttype default ‘~na~’,
[strscriptran] [nvarchar] (150) not null constraint def_tblBuild_strscriptran default ‘~na~’
) on [primary]
go
declare @build [nvarchar] (150)
set @build = N’yourdb db script 1.0′
insert into tblBuild (strscripttype, strscriptran) values (@build, (cast(getdate() as nvarchar)))
–select * from tblBuild

---

Possibly related posts: (automatically generated)

• Installing AdventureWorks Database