Microsoft provide a sample database called AdventureWorks.
We can download it from ” Github Links ” which also provide OLTP and Data Warehouse sample database.

We need to download “AdventureWorks2016.bak”, it is backup file of sample database.
We just need to restore it to our SQL Server.

We will place the backup file in share folder.
We cannot restore backup file in share folder directly, we need to map share folder to SQL server first.
You can view this post on ” How to map network driver in SSMS “

Data path and Log path save in MS SQL’s backup file.
When we restore the backup, these paths must be existed. If not, the restore will fail.
By default, when we restore backup by GUI, SSMS will specify the path to default path if these paths do not exist.
When you restore backup by T-SQL, you need to specify the path.

We can get the information of backup such as the database name, filename, file path by:

-- Get Database Name in the backup file
RESTORE HEADERONLY from disk='Z:\AdventureWorks2016.bak'
-- Get File Name and Path in the backup file
RESTORE FILELISTONLY from disk='Z:\AdventureWorks2016.bak'
-- Get the default path
SELECT
SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,
SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath

We will use restore the backup with the new database and the new data path and log. You need to create the new path before you restore the backup.
In the following code, we will restore backup to new database named “TEST_DB” and specify the new data path and log path to “C:\TEST_DB”

-- Create database for restore
CREATE DATABASE TEST_DB
-- Restore backup to other database
RESTORE DATABASE TEST_DB FROM DISK='Z:\AdventureWorks2016.bak'
WITH REPLACE,
MOVE 'AdventureWorks2016_Data' TO 'C:\TEST_DB\TEST_DB.mdf',
MOVE 'AdventureWorks2016_Log' TO 'C:\TEST_DB\TEST_DB.ldf'
-- Delete network drive

How to install sample database by GUI: