In this guide, we will walk through the complete process of setting up Microsoft SQL Server. We will cover the installation of the database engine and management tools, importing a sample database, enabling remote access, creating user accounts, and performing backups.
We start by installing the core components: the SQL Server Database Engine and the SQL Server Management Studio (SSMS).
Download the free "Developer" edition from the official Microsoft website. This version is fully featured but licensed for non-production environments. Run the installer and select the Basic installation type to set up the database engine with default settings.
After the engine installation is complete, click the "Install SSMS" button (or download it separately). SSMS is the graphical interface used to manage your databases. Install it with default settings.
Once installed, launch SSMS. Connect using "Windows Authentication" to log in with your current Windows user credentials.
Download the "AdventureWorksLT2022" backup file from Microsoft's official GitHub or documentation page. Move the downloaded .bak file to a simple path like C:\Temp to make it easier to locate.
In SSMS, right-click on Databases and select Restore Database. Choose Device, click the three dots (...), select Add, and browse to your .bak file. Click OK to import the database.
By default, SQL Server only allows Windows Authentication. To allow external users (like "Timmy") to connect using a username and password, we must enable Mixed Mode Authentication.
Right-click the server name in SSMS, go to Properties > Security, and select SQL Server and Windows Authentication mode. Restart the SQL Server service (via services.msc) for this change to take effect.
In SSMS, expand Security > Logins. Right-click and select New Login. Select "SQL Server authentication", enter a username (e.g., "SuperTimmy"), and set a password. Grant the user the sysadmin server role to give them full control.
If a remote user tries to connect now, they will likely fail. We need to enable TCP/IP and open the firewall.
Open SQL Server Configuration Manager. Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER. Right-click TCP/IP and select Enable. Restart the SQL Server service again.
Open "Windows Defender Firewall with Advanced Security". Create a new Inbound Rule for Port 1433 (TCP), which is the default SQL Server port.
Security Tip: To restrict access, go to the rule's properties, click the Scope tab, and add only the specific IP address of the remote user (e.g., Timmy's IP) under "Remote IP addresses".
Finally, let's perform a manual backup.
Right-click your database (AdventureWorks), select Tasks > Back Up. Choose "Copy Only" backup type if you don't want to interrupt the backup chain. Select the destination, give the file a name (e.g., AW_Backup.bak), and under options, you can choose to compress the backup to save space. Click OK to complete the backup.