Home > Database Administration, SQL Server 2008R2 > Creating & Importing Databases – SQL Server 2008R2

Creating & Importing Databases – SQL Server 2008R2

SQL Server is a Relational Database Management System (RDBMS), so being able to create and import databases is an essential function of all Database Administrators. Today, we’ll take a look at the different methods of creating and importing databases in SQL Server 2008 R2. There are multiple ways to create and import databases, including wizards, menu options, and coding. We’ll cover each in this lesson.

To begin this lesson, download the AdventureWorks2008R2_Database.zip from Microsoft.  It’s a 36 MB download.  When complete, open the .zip file and unpack the contents to an easy to remember directory.  Alternatively, the files (The database file and the log file) can be extracted to the default SQL 2008 Data Folder, typically located at in Program Files –>Microsoft SQL Server –> MSSQL10_50.<Instance Name> –> MSSQL –> Data.

I.  Importing an MDF/LDF File (AdventureWorks)

Select Attach... from the menu

Figure I.A

A.  Open the SQL Server Management Studio and log in to your SQL Server.  To import the AdventureWorks database, right-click on “Databases” in the Object Explorer and select “Attach…”.

Figure I.B

B.  In the “Attach Databases” window, select “Add…” and select the location to which you unzipped the AdventureWorks database, selecting the .mdf file.  The .ldf log file will automatically be selected in the “Database Details” window.

Figure I.C

C.  Click “OK” to attach the database, and viola!  You’ve successfully added a database to SQL Server.

II.  Creating Database From Scratch (SQL Script)

NOTE:  There are many options for creating a database from by creating a SQL script.  I will cover a basic database here.  For full details of all options, please view the MSDN article about CREATE DATABASE (Transact-SQL).

Figure II.A

A.  Open a new script window by selecting “New Query” from the toolbar.  This will open the Script Editor.  

Figure II.B

B.  Use a CREATE DATABASE code similar to the one above, pressing F5 to execute the code.  Using this option, all filenames, paths and options will be set to default.

Presto!  That was pretty simple!  There’s one more method to cover, and that’s the Create Database Wizard.

III.  Create Database Wizard

Use the Create Database Wizard to be able to set advanced options, or as a simple way to create a database and specify administrators.

Figure III.A

A.  Right-click on “Databases”  and select “New Database”.  This opens the Create Database Wizard. 

Figure III.B

B.  In the New Database window, specify a name for your database, and assign an administrator.  Leaving this as “<default>” will set the current user as the administrator.  To allow anyone logged in administration rights, select the Ellipses (“…”), and then “Browse”.  Select the “[NT Authority]/[NetworkService]” checkbox.  Select “OK”, and your database will be created.

Figure III.C

C.  Refresh the database by right-clicking on Databases”  and selecting “Refresh”.  Your database is now visible, and tada! You’ve again created a database!

 

That concludes today’s lesson on creating and importing a database.  Next time, we’ll look into basic querying, including selecting from multiple tables (joins).

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: