Lab: Using SQL Server Administrative Tools

Scenario

MCT USE ONLY. STUDENT USE PROHIBITED

1-16 Introduction to SQL Server 2014 Database Administration

As a new database administrator at Adventure Works Cycles, you plan to familiarize yourself with the SQL
Server instance and tools that you will use to manage databases.

Objectives
After completing this lab, you will be able to:

Use SQL Server Management Studio.

Use the sqlcmd utility.

Use Windows PowerShell with SQL Server.

Estimated Time: 45 Minutes

Virtual machine: 20462C-MIA-SQL
User name: ADVENTUREWORKS\Student
Password: Pa$$w0rd

Exercise 1: Using SQL Server Management Studio

Scenario
The DBAs at Adventure Works Cycles use SQL Server Management Studio as the primary administrative
tool for SQL Server databases. You therefore want to familiarize yourself with it.
The main tasks for this exercise are as follows:
1. Prepare the Lab Environment
2. Use Object Explorer in SQL Server Management Studio
3. Create a Database
4. Run a Transact-SQL Query
5. Create a Project
Task 1: Prepare the Lab Environment
1.

Ensure that the 20462C -MIA-DC and 20462C -MIA-SQL virtual machines are both running, and then
log on to 20462C -MIA-SQL as ADVENTUREWORKS\Student with the password Pa$$w0rd.

2.

Run Setup.cmd in the D:\Labfiles\Lab01\Starter folder as Administrator.

Task 2: Use Object Explorer in SQL Server Management Studio

1.

Start SQL Server Management Studio and connect to the MIA-SQL database engine using Windows
authentication.

2.

Ensure that Object Explorer is visible, and expand the Databases folder to view the databases that are
hosted on the MIA-SQL instance.

3.

View the Server Dashboard standard report for the MIA-SQL instance.

Task 3: Create a Database

1.

Under MIA-SQL right-click the Databases folder, and click New Database. Then create a new
database named AWDatabase with the default settings.

2.

View the databases listed under the Database folder and verify that the new database has been
created.

Task 4: Run a Transact-SQL Query

1.

MCT USE ONLY. STUDENT USE PROHIBITED

Administering Microsoft SQL Server Databases

1-17

In SQL Server Management Studio, create a new query, and execute the following Transact-SQL code:
EXEC sp_helpdb AWDatabase;

2.

View the query results, noting that they include information about the AWDatabase you created in
the previous task.

3.

Save the script file as GetDBInfo.sql in the D:\Labfiles\Lab01\Starter folder.

Task 5: Create a Project

1.

In SQL Server Management Studio, create a new project named AWProject in the
D:\Labfiles\Lab01\Starter folder.

2.

Ensure that Solution Explorer is visible, and add a new connection to the project. The new connection
should be to the MIA-SQL database engine instance and it should use Windows authentication.

3.

Add a new query to the project, and change its name to BackupDB.sql.

4.

In Object Explorer, right-click the AWDatabase database you created previously, point to Tasks, and
click Back Up.

5.

In the Back Up Database AWDatabase dialog box, in the Script drop-down list, select Script
Action to Clipboard. Then cancel the backup operation.

6.

Paste the contents of the clipboard into the empty BackupDB.sql script.

7.

Edit the BackupDB.sql script to change the backup location to

D:\Labfiles\Lab01\Starter\AWDatabase.bak.

8.

Save all of the files in the solution, and then close the solution and minimize SQL Server Management
Studio.

Results: At the end of this exercise, you will have created a SQL Server Management Studio project
containing script files.

Exercise 2: Using the sqlcmd Utility

Scenario
DBAs at Adventure Works Cycles occasionally use sqlcmd to connect to SQL Server and perform
maintenance tasks. You therefore want to familiarize yourself with sqlcmd.
The main tasks for this exercise are as follows:
1. Use sqlcmd Interactively
2. Use sqlcmd to Run a Script

Task 1: Use sqlcmd Interactively

1.

Open a command prompt, and enter the following command to view details of all sqlcmd
parameters:
sqlcmd -?

2.

Enter the following command to start sqlcmd and connect to MIA-SQL using Windows
authentication:
sqlcmd -S MIA-SQL -E

3.

In the sqlcmd command line, enter the following commands to view the databases on MIA-SQL.
Verify that these include the AWDatabase database you created in the previous exercise:
SELECT name FROM sys.sysdatabases;
GO

4.

Enter the following command to exit sqlcmd:

Exit

Task 2: Use sqlcmd to Run a Script

1.

In the command prompt window, enter the following command to use sqlcmd to run the
GetDBInfo.sql script you created earlier in MIA-SQL:
sqlcmd -S MIA-SQL -E -i D:\Labfiles\Lab01\Starter\GetDBinfo.sql

Note that the query results are returned, but they are difficult to read in the command prompt screen.
2.

Enter the following command to store the query output in a text file:
sqlcmd -S MIA-SQL -E -i D:\Labfiles\Lab01\Starter\GetDBinfo.sql -o
D:\Labfiles\Lab01\Starter\DBinfo.txt

3.

Use Notepad to view the contents of the D:\Labfiles\Lab01\Starter\DBinfo.txt file.

Results: At the end of this exercise, you will have used sqlcmd to manage a database.

Exercise 3: Using Windows PowerShell with SQL Server

Scenario

MCT USE ONLY. STUDENT USE PROHIBITED

1-18 Introduction to SQL Server 2014 Database Administration

IT administrators at Adventure Works use Windows PowerShell to script configuration tasks across a range
of services. You want to investigate how to use Windows PowerShell with SQL Server.
The main tasks for this exercise are as follows:
1. Use Windows PowerShell
2. Using PowerShell in SQL Server Management Studio.
3. Create a PowerShell Script

Task 1: Use Windows PowerShell

1.

On the taskbar, click the Windows PowerShell icon.

2.

At the Windows PowerShell prompt, enter the following command:

Get-Process

3.

Review the list of services. In the ProcessName column, note the SQL services. Then enter the
following command to list only the services with names beginning SQL,:
Get-Process SQL*

4.

To find a way to sort the list, enter the following command:

Get-Help Sort

5.

Review the help information, then enter the following command:

Get-Process SQL* | Sort-Object Handles

6.

Verify that the list is now sorted by number of handles, and close Windows PowerShell.

Task 2: Using PowerShell in SQL Server Management Studio.

MCT USE ONLY. STUDENT USE PROHIBITED

Administering Microsoft SQL Server Databases

1-19

1.

In SQL Server Management Studio, in Object Explorer, right-click MIA-SQL, and then click Start
PowerShell.

2.

Enter the following command to show which modules are loaded, and verify that they include SQLPS
and SQLASCMDLETS:
Get-Module

3.

Enter the following command to set the current location to the MIA-SQL server:
Set-location SQLServer:\SQL\MIA-SQL

4.

Use the following command to display the SQL Server database engine instances on the server:
Get-ChildItem

5.

Use the Set-Location cmdlet to change the current location to SQLServer:\SQL\MIASQL\DEFAULT\Databases.

6.

Use the Get-ChildItem cmdlet to display the databases on the default instance.

7.

Use the following command to execute a Transact-SQL statement that retrieves the server version:
Invoke-Sqlcmd "SELECT @@version"

8.

Close the SQL Server Powershell window and close SQL Server Management Studio without saving
any files.

Task 3: Create a PowerShell Script

1.

On the task bar, right-click the Windows PowerShell icon and click Windows PowerShell ISE.

2.

In the PowerShell command prompt, enter the following command to verify that the SQLPS module
is not loaded:
Get-Module

3.

Use the following command to load the SQLPS module, and then use the Get-Module cmdlet to
verify that it has been loaded:
Import-Module SQLPS -DisableNameChecking

MCT USE ONLY. STUDENT USE PROHIBITED

1-20 Introduction to SQL Server 2014 Database Administration

4.

If the Commands pane is not visible, on the View menu, click Show Command Add-on. Then in the
Commands pane, in the Modules list, select SQLPS and view the cmdlets in the module, noting that
they include cmdlets to perform tasks such as backing up databases and starting SQL Server
instances.

5.

If the Script pane is not visible, click the Script drop-down arrow. Then, in the Script pane, type the
following commands. (Hint: Use the IntelliSense feature.)
Import-Module SQLPS -DisableNameChecking
Set-location SQLServer:\SQL\MIA-SQL\Default\Databases
Get-Childitem | Select Name, Size, SpaceAvailable, IndexSpaceUsage | Out-GridView

6.

Click Run Script. Then view the results in the window that is opened. (The script may take a few
minutes to run.)

7.

Close the output window, and modify the script as shown in the following example:
Import-Module SQLPS -DisableNameChecking
Set-location SQLServer:\SQL\MIA-SQL\Default\Databases
Get-Childitem | Select Name, Size, SpaceAvailable, IndexSpaceUsage | Out-File

'D:\Labfiles\Lab01\Starter\Databases.txt'
8.

Save the script as GetDatabases.ps1 in the D:\Labfiles\Lab01\Starter folder. Then close the
PowerShell ISE.

9.

In the D:\Labfiles\Lab01\Starter folder, right-click GetDatabases.ps1 and click Run with PowerShell.

10. When the script has completed, open Databases.txt in Notepad to view the results. Then close
Notepad.

Results: At the end of this task, you will have a PowerShell script that retrieves information about
databases from SQL Server.