innovationtip

Main Menu

Share innovation with the world

innovationtip / Install SQL Database T-SQL / Install Adventure Works database from backup

Install Adventure Works database from backup

The Adventure Works 2014 Data Warehouse database can be installed by restoring a database backup.

1. Download Adventure Works DW 2014 Full Database Backup.zip .

2. From File Download, click Save. Once it is saved, open the folder.

3. Extract the AdventureWorks2014.bak file to a location on your local server.

Note: The default 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup.

Use C:\Program Files (x86)\… for 32-bit SQL Server 2014.

4. From SQL Server Management Studio connect to the 2014 instance.

5. On the Standard toolbar, click the New Query button.

Execute the following code in the query window: Note: The file paths in the scripts are the default paths. You may need to update the paths in the scripts to match your environment.

USE [master]

RESTORE DATABASE AdventureWorksDW2014

FROM disk= ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2014.bak’

WITH MOVE ‘AdventureWorksDW2014_data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014.mdf’,

MOVE ‘AdventureWorksDW2014_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2014.ldf’

,REPLACE

As an alternative to steps 5 and 6, you can restore the database using the SQL Server Management Studio user interface. For more detailed information, see below

Download Adventure Works 2014 Full Database Backup file from CodePlex The compress file contains the backup file of sample database AdventureWorks2014. The total download size of the sample database is around 44 MB.

After you complete the zipped sample database download file containing AdventureWorks2014 data file, extract the contents to a new folder. Move the backup file AdventureWorks2014.bak which is about 44.5 MB to Backup folder of your SQL Server 2014 database instance. Since I installed SQL Server 2014 to a named instance (SQLServer2014), I moved the backup file AdventureWorks2014.bak to default Backup folder which is at file path:
C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Backup

  1. Then open SQL Server Management Studio (SSMS).
  2.  Connect to SQL Server 2014 database instance where you want to install the sample database AdventureWorks download
  3.  Highlight Database node under the SQL Server instance in Object Explorer windows of SQL Server Management Studio (SSMS)
  4.  Right click on the Database node, select Restore Database

 

When Restore Database dialog screen is displayed, choose Device option from Source section. Device option enables you to point to the storage device where the database backup is stored.
Click on the “…” three point button to select the backup device.

If the current place is not listed in the existing devices, press on Add button.
Locate and choose “C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Backup\AdventureWorks2014.bak” as the backup file source.
Please note that in the file folder path “MSSQL12.SQLSERVER2014”, [SQLSERVER2014] is the database instance name.

You will see that as the Destination Database, AdventureWorks2014 is already set automatically after the backup file selection.

Although this database does not exist in our SQL Server 2014 instance, after restore operation is completed it will be created and restored from the database backup file.

 

 

If all database restore operation is successfully completed, our SQL Server 2014 sample database AdventureWorks2014 will be created and populated with sample data.

 

 

 

 

 

 

RELATED POSTS