SQL Server 2022 Installation (Part 2 - Setting Up SSMS and Backing Up A Database)

In this video, Manuel Quintana provides a step-by-step guide to installing SQL Server Management Studio (SSMS), connecting to a SQL Server instance, and backing up a database. This post will walk you through the process and highlight the key steps involved in setting up SSMS, restoring a database, and preparing for further SQL Server work.

Step 1: Installing SQL Server Management Studio (SSMS)

Manuel starts by explaining that if you’ve followed Part 1 of the series, you should already have SQL Server installed on your system. However, if you're only interested in setting up SSMS, you can skip to this step. SSMS is an essential tool for managing SQL Server instances, creating and modifying databases, and running queries. Here’s how you can install it:

• Download SSMS: Go to the official Microsoft page for SSMS. Select the latest version (18.12.1) to download, which includes Azure Data Studio (ADS) for cloud-based operations.
• Install SSMS: Launch the installer and follow the on-screen prompts. The installation process is quick, and you can leave the default settings unless you need to specify a different installation directory. Once completed, you will have both SSMS and Azure Data Studio installed.

Step 2: Launching SSMS and Connecting to SQL Server

After installation, you can launch SSMS. Upon opening the application, you are prompted to connect to a SQL Server instance.

• Connecting to Your Instance: By default, SSMS will try to connect to the local SQL Server instance on your machine. In the connection dialog, you can simply enter a period (".") or "localhost" to connect to the default instance. If you set up a named instance during installation, you will need to provide the specific name of that instance.
• Authentication: SSMS uses Windows Authentication by default. This means if you installed SQL Server with admin rights, SSMS will automatically log you in with your Windows credentials.
• Exploring SSMS: Once connected, you can see the object explorer panel on the left side. From here, you can view databases, manage security, and perform other administrative tasks.

Step 3: Restoring a Database in SSMS

At this point, you have a blank SQL Server instance with no databases. To get started with querying and learning, it’s essential to restore a sample database. Manuel demonstrates how to restore the AdventureWorks database, which is commonly used in SQL Server courses.

• Downloading the AdventureWorks Database: Go to Google and search for "Download AdventureWorks Database" or visit the Microsoft site to find various versions. For this video, Manuel chooses the AdventureWorks 2019 OLTP version, a transactional version used for learning and testing SQL queries.
• Restoring the Database: Once the database is downloaded (in a .bak file format), you can restore it into your SQL Server instance by following these steps:
  1. Right-click the Databases node in the Object Explorer and select Restore Database.
  2. In the Restore Database window, select the Device option and then click the ellipsis (…) button to browse for the .bak file.
  3. Navigate to the location of the file (e.g., your Downloads folder) and select the AdventureWorks .bak file.
  4. Click OK to restore the database.
• Verifying the Restoration: After restoring, you should see the AdventureWorks 2019 database in the Object Explorer. Expand the database to see the tables, views, and stored procedures that you can now query and explore.

Step 4: Conclusion

With SSMS installed, connected to a SQL Server instance, and a sample database restored, you’re now ready to begin working with SQL Server. In this video, Manuel Quintana has shown how to:

• Install SQL Server Management Studio (SSMS).
• Connect to your SQL Server instance.
• Restore a sample database to start querying.

These are the foundational steps needed for further learning in SQL Server. Whether you’re diving into T-SQL or exploring Power BI, these tools will serve as the basis for many of the courses offered by Pragmatic Works.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on SQL Server and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.