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

About these ads

16 thoughts on “Creating a SQL Server database by script

  1. Pingback: SQL Server 2005 file sizes and filegrowth « Golbing

  2. Is there a way to use a default path and file name? What if you want to distribute the script to different machines that have different paths to where SQL server is installed?

  3. Bill.
    Your SQL Server engine would need to have knowledge of the operating system it was being installed on. That is possible but I don’t like opening up a security hole like that so I’ve never done it. But I’m sure someone out there has.

    Terry

  4. Hi,

    I am new to SQL Server development. But I have a website that has some Ms Access databases interactivities. Now on my localhost, all these functionalities and database interactivities are working well, but when I uploaded the website to my online web server, the Ms Access databases are no longer working. And my web hosting account has Ms SQL server on it, which means that I can upgrade my Ms Access Database to Ms SQL Server on the web.

    My questions/predicament now is: HOW DO I RUN THE SQL SEVER SCRIPTS ONLINE?

    Please HELPP!!!!

    • Your web hosting service provider is going to have to help you with that one. Sometimes they require the scripts and will run them for you or they will provide you with an account with limited rights so you can do it yourself. They will sometimes provide some kind of interface for you that prevents problems in the back end. I hope that I have answered your question.

  5. Pingback: 2010 in review « Golbing

  6. I simply needed to thank you very much once again. I’m not certain the things that I would have taken care of without the ways provided by you about this area of interest. This has been the frightening dilemma for me, however , taking note of a new specialized way you solved it forced me to weep over gladness. I’m happy for the help as well as expect you recognize what a great job that you’re providing training some other people through the use of your webpage. Probably you’ve never met all of us.

    • It’s not my ‘site’. This is run by WordPress and they let me blog here.

      The theme I use is supposed to have mobile options and they’re all turned on as far as I can see.

  7. Hey! I know this is sort of off-topic however I needed to ask.
    Does running a well-established blog such as yours
    take a large amount of work? I’m brand new to operating a blog however I do write in my journal daily. I’d like
    to start a blog so I can easily share my personal experience and thoughts online.
    Please let me know if you have any kind of ideas or tips for new aspiring bloggers.
    Appreciate it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s