SQL Server 2022 Manual Setup

The steps below describe how to do a manual installation of the database. This is the recommended method for development, and the required method if you intend to make your server publicly accessible. If you're comfortable using Docker, there is a development-only database solution available in this project. See: Docker README.md

These instructions were written for Windows. SQL Server has support for some Linux distros (See Microsoft's Installation Guidance for Linux.)

Install Microsoft SQL Server

Download and run the Microsoft SQL Server Express 22 installer.

1. Select Custom install:
   
2. Update the installation location if desired and click install.
3. The SQL Server Installation Center will pop up after install is selected. Click "New SQL Server standalone installation or add features to an existing installation":
   
4. Agree to the License if you agree with it.
5. Enable SQL Server updates via Microsoft Updates if desired and click Next.
6. Click "Next" on the Install Rules page.
7. We don't need/use Azure, so uncheck the extension installation unless you use it for some other project. Click Next.
   
8. You can uncheck "Machine Learning Services" on Feature Selection.
   
9. On the Instance Configuration page, it is recommended to keep the default of "SQLExpress". If you do change this, make sure to keep note of it as you'll need it to connect later on. Click Next.
10. On the "Server Configuration" page, click the Collation tab and ensure it is set to "Latin1_General_CI_AS". Click Next.
    
11. On Database Engine Configuration, select "Mixed Mode" so that both Windows Auth and SQL auth are supported. Set a password for your "sa" account. Click Next.
    
12. SQLExpress should now work through the rest of its installation. It may take a few minutes. The Complete screen should be shown as follows when it is done:
    
13. Click the Close button. Close SQL Server Installation Center.

Install SQL Server Management Studio

Download and install SQL Server Management Studio.

Visual Studio Installer should launch.

There are no required workloads or components, you can just click install.

Restore our database

Download our database.

1. Create the folder "C:\DB" and extract the database here:
   
   The reason for this is that when we go to restore it, SQL Management Studio will not be able to access anything user-specific, so we'll put it in an easy-to-access folder that it can.
   

2. Run the SQL Server Management Studio:
   
   

3. On first run you'll get a sign in screen; press "Skip and add accounts later.":
   

4. It may prompt you to switch between a new or old connection dialog 'experience'. Just press "Yes":
   

5. We need to connect to our server:
   The new connection dialog looks like this:
   
   The old connection dialog looks like this:
   
   
   Server Name: Either ".\SQLEXPRESS" if we installed SQL Server Express, or "." if we installed another edition like "Developer"
   
   Authentication: If the guide was followed above, mixed auth mode was enabled, so you can leave it on "Windows Authentication".
   
   Encrypt (or "Encryption"): Set this to "Optional".
   
   Press "Connect".

6. Now that you're connected, right-click "Databases" and select "New Database...":
   

7. When prompted, set the "Database name" to "KN_online" and press "OK":
   

8. Now you should see "KN_online" under "Databases". Right-click "KN_online", go "Tasks", then "Restore" and finally "Database...":
   

9. Under "Source", tick "Device" and press the "..." next to it on the right:
   

10. Press "Add":
    

11. Browse to "C:\DB" (that we created earlier and extracted our database backup to) and select "KN_online_dev.bak":
    
    Press "OK".

12. Press "OK" again on the "Select backup devices" dialog:
    

13. Select the "Options" page:
    

14. On the "Options" page:
    
    

    • Tick "Restore options" -> "Overwrite the existing database (WITH REPLACE)"
    • Untick "Tail-Log backup" -> "Take tail-log backup before restore"
    • Tick "Server connections" -> "Close existing connections to destination database"
      Press "OK".

15. It should restore correctly. Press "OK" once more to close the confirmation:
    

The database is now restored.