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;

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

— 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
( 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;

— 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’;

use yourdb

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

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]
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 tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , . Bookmark the permalink.

16 Responses to Creating a SQL Server database by script

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

  2. bill says:

    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. tgrignon says:

    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.


  4. Alex says:

    Is there also a way to create more than one database in one script?

  5. tgrignon says:

    Sure. Just copy the script and put it below and change the name of the database for every ocurrence of that name.

  6. heru says:

    makasih sharingnya…

  7. Cas says:


    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!!!!

    • tgrignon says:

      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.

  8. Pingback: 2010 in review « Golbing

  9. Vivekanand says:

    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.

  10. click here says:

    How come you dont have your web site viewable in wap format? cant see anything in my iPhone.

    • tgrignon says:

      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.

  11. What’s Taking place i’m new to this, I stumbled
    upon this I’ve discovered It absolutely useful and it has helped me out loads. I hope to give a contribution & assist different customers like its helped me. Good job.

  12. I’m not that much of a internet reader to be honest but your blogs really nice, keep it up!
    I’ll go ahead and bookmark your website to come back in the future. All the best

  13. 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: Logo

You are commenting using your 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