mssqltips logo

Create SQL Server Database using SQL Server Management Studio

By:   |   Updated: 2019-10-22   |   Comments   |   Related: More > Database Administration

Problem

You're new to SQL Server and need to create a database. It sounds like a simple enough task, but how do we do it?

Solution

Let's step through the database creation process using SQL Server Management Studio (SSMS).

Note: If you're creating a database for the very first time and you have the rights to do it, it's assumed you're doing it on a test or development server and not on a production server.

Versions used here are:

  • SQL Server 2017 Cumulative Update (CU) 16 Developer Edition installed on my laptop
  • SQL Server Management Studio version 18.2 (latest as of this writing)

First, a little background on SSMS. It's the primary tool used to manage SQL Server. It comes with a vast amount of functionality built in to manage servers and develop SQL scripts. It started life as SQL Server Enterprise Manager and grew into SSMS as of version 2005. And prior to SQL Server 2016, SSMS was packaged with the SQL Server install media and it required the media to install it. As of SQL Server 2016 it's now a separate download. And even better, it's free. You can find the documentation and download link to the latest version here. SSMS has a short development cycle and new versions come out about every couple of months or so and the latest version will support all supported versions (and probably unsupported ones as well but we don't have any of those in our environment of course) of SQL Server. To install it, just click on the download link to get the installer. Execute SSMS-Setup-ENU.exe as an administrator and follow the prompts. It takes a little while to complete but when it's done, you'll have the latest SSMS on your machine.

Now that we know what the tool is and where it came from, we can get to work.

Creating a SQL Server Database with SSMS

To open SSMS:

  1. All Apps
  2. Microsoft SQL Servers Tools
  3. Microsoft SQL Server Management Studio
Start SQL Service Management Studio (SSMS)
  1. Click Connect in the Object Explorer and choose Database Engine…
Connect to database engine
  1. Enter SQL Server name you're creating database on
  2. Choose authentication method
  3. Enter credentials (if you choose Windows Authentication this will automatically be populated with your Windows credentials) that has rights to create databases
Connect to SQL Server
  1. Right click on Databases
  2. New Database…
New database
  1. Enter database name (this will populate the Logical Name fields)
  2. Leave owner as <default> and you will be the owner or enter another login if needed
New database general options

You could just click OK and the database will be created with the defaults. However, we probably want to go a bit further before doing the create as the default settings aren't necessarily the best settings, so let's continue.

Additional New SQL Server Database Configuration Options

  1. Database Name
  2. Owner
  3. Set data file size to determine the initial file size of the data file – You could just leave it at the default, but it's best it's best to size it according to what you think it needs initially so it won't automatically grow right away.
  4. Set transaction log file size to determine the initial file size of the transaction log file – Same as with the data file, it's best it's best to size it according to what you think it needs initially so it won't automatically grow right away.
  5. Click this ellipse to set how much the data file will automatically grow by in the Change Autogrowth box that opens – SQL Server 2017 defaults to 64 MB. This is better than earlier versions, some of which defaulted to automatically grown by 1 MB at a time which meant there was a performance hit as the transaction stops while the file grows every time the file needed to grow by 1 MB which would be frequently.
  6. Click this ellipse to set how much the log file will automatically grow by in the Change Autogrowth box that opens – Same as data file growth.
  7. Make any changes to path for data file – I'm creating this database on my laptop, so everything is on the C:\ drive. You never want to see this this on a server. Typically, you create data files on one drive, say E:\ and logs on another, maybe F:\. C:\ is for the operating system.
  8. Make any changes to path for log file – As stated in the previous step I'm creating this database on my laptop, so everything is on the C:\ drive, but it bears repeating.
  9. Options for additional options see the next image below:
Name new database plus changes to default options

The Options settings will typically remain at the defaults.

  1. But the Recovery model could be changed here in the event transaction log backups are not taken to prevent the log file from growing out of control i.e. the database will be strictly used for testing. Otherwise transaction log backups will be needed.
  2. Change Compatibility Level to a lower version of SQL Server
Other database options

Summary of New SQL Server Database Settings

I've named my database MyDatabase, chosen sa as the owner, set initial data file size to 1024MB (1 GB), initial transaction log file size to 512 MB (0.5 GB), data file to automatically grow by 256 MB (0.25 GB) and log file to grow by 128 MB (0.125 GB).

Name new database plus changes to default options with changes made

And on the Options page, I've set the Recovery model to Simple as I'm only going to be using this database for some basic testing and won't be backing up the transaction because there is no point in time recovery needed.

Other database options with changes made

Time to hit OK to create the database.

Check New SQL Server Database in Object Explorer in SSMS

If we look under Databases in SSMS we can see the new database we created called MyDatabase.

Newly created database
  1. Right click on our new database
  2. Choose Properties
Database properties

The Files tab will show us the name, owner, file sizes and autogrow increments we chose. Also, we can see that it chose the physical file names based on the name we chose for the database.

Database properties Files tab

And the Options page shows where we changed the Recovery model to Simple.

Database properties Options tab

There are several other options for a database, but we'll leave that for another tip on another day.

Next Steps

Following are some links to more detailed information on SSMS, creating databases and recovery models:



Last Updated: 2019-10-22


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools