SQL Homework – May 2018 – Create a database


May 2, 2018 by Kenneth Fisher

I’ve really got to make a plan here at some point. So far I’ve been just putting down things as I think of them. In September I had you set up a home lab, and in October I had you download and attach some sample databases. What would have been the next logical step? Creating a database of your own of course. So here we are 7 months later and it’s time to create your own database!

Things to make you go hmmm.
  • What are the default locations for the data and log files.
  • How big is our database going to be?
  • Do the default locations have sufficient space or should we get more? Or maybe put this database on it’s own set of drives?
  • Do we have an estimate for the growth over the next month (during setup)? Year? 5 years?
  • Are we going to need in-memory tables? Filestream?
  • What type of recovery do we need? Full (point in time recovery) or Simple (recover only to the times full and differential backups ended)?
  • Who is going to need access to this database? (This is a test database so just us obviously.)
  • Do we need any database level configuration changes to be different from the default? (compatability level, MAXDOP, collation, etc)
  • Depending on your needs you might even be asking: Should this database be in the cloud?


Requirements for your new database.

(10 points for each task.)

  • The database should have 3 filegroups. One of them read only.
  • The non-PRIMARY read/write filegroup should have two files.
  • The non-PRIMARY read/write filegroup is the default filegroup.
  • Make each new data file 100mb (leave PRIMARY as the default) and the log file 50mb.
  • The size for the file in the read only filegroup should have a max size of 100mb.
  • The autogrowth on the log should be 10mb with a max of 1gb.
  • The autogrowth on the PRIMARY filegroup should be 50mb with a max of 500mb.
  • The autogrowth on the non-PRIMARY filegroup should be 100mb with a max of 10gb.
  • The database should be in SIMPLE recovery.
  • Make the collation SQL_Latin1_General_CP1_CS_AS.
  • MAXDOP should be 2.


Now if you want to get ahead, next month I will have you take this database, and change literally each of these settings.

And as always, yes, I realize that if you are a Sr DBA all of this is easy stuff. This isn’t really targeted at you. It’s meant for Jr and Mid-level DBAs who may not be quite as comfortable with some of these tasks. That said, if you are a Sr DBA, I challenge you to create this database through a script, without using BOL.

4 thoughts on “SQL Homework – May 2018 – Create a database

  1. Сергей Скрипник says:
    CREATE DATABASE [sqltest]
    ( NAME = N'sqltest', FILENAME = N'd:\data\sqltest\sqltest.mdf' , SIZE = 102400KB , MAXSIZE = 512000KB , FILEGROWTH = 51200KB ), 
    ( NAME = N'sqltest_nonpr_f1', FILENAME = N'd:\data\sqltest\sqltest_nonpr_f1.ndf' , SIZE = 102400KB , MAXSIZE = 102400KB , FILEGROWTH = 51200KB ),
    ( NAME = N'sqltest_nonpr_f2', FILENAME = N'd:\data\sqltest\sqltest_nonpr_f2.ndf' , SIZE = 102400KB , MAXSIZE = 102400KB , FILEGROWTH = 51200KB ), 
    ( NAME = N'sqltest_nonpr2_f1', FILENAME = N'd:\data\sqltest\sqltest_nonpr2_f1.ndf' , SIZE = 102400KB , MAXSIZE = 10485760KB , FILEGROWTH = 102400KB ),
    ( NAME = N'sqltest_nonpr2_f2', FILENAME = N'd:\data\sqltest\sqltest_nonpr2_f2.ndf' , SIZE = 102400KB , MAXSIZE = 10485760KB , FILEGROWTH = 102400KB )
     LOG ON 
    ( NAME = N'sqltest_log', FILENAME = N'd:\data\sqltest\sqltest_log.ldf' , SIZE = 51200KB , MAXSIZE = 1048576KB , FILEGROWTH = 10240KB )
     COLLATE SQL_Latin1_General_CP1_CS_AS
    USE [sqltest]
    USE [sqltest]
    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'non-PRIMARY') ALTER DATABASE [sqltest] MODIFY FILEGROUP [non-PRIMARY] DEFAULT
  2. […] a pointed reminder that they may not know everything (oops, missed that). Last month I asked you to create a database with a rather specific set of requirements. This […]

  3. SQLMac says:

    I didn’t think too much of this challenge when I first saw it. I mean I’ve done this thousands of times via SSMS. Then I thought about it some more and realized, I have never done it from VIsual Studio. Thanks for putting a new spin on regular work Kenneth.


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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: