If you have started working on SQL server(any version), at some point you may need to transfer your database from one pc to another after taking backup of sql database.

Usually, we restore the database either using scripts or using .bak file of the database, Executing is script is quite easy, just open a 'New Query' window in your SQL server version and copy-paste your script which may have data or just tables schema(as selected by your while creating scripts), so in this post, I will explain you about restoring the database using .bak in SQL server, step by step with images.

Step 1: I suppose you already have the .bak file of the database which you want to restore. If you don't have it, create .bak file using sql server or just download the demo database from this link https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

Step 2: Move your .bak in a drive/folder which can be accessed by anyone or you can say which doesn't require any special rights.

Step 3: Open your SQL server(Express version or any other version I am using Express version), connect with your sql server for which you want to restore the database, right-click on the "Databases" and click "Restore Database"

attaching-bak-file-in-sql-server-1-min.png

Step 4: Now select the "Device" radio button and then click on browse(...) button to locate the .bak file stored in your hard drive.

restore-bak-file-in-sql-server-2-min.png

Step 5: As soon as you will click browser(...) but, a dialog box like below will appear, click "Add" inside that dialog box

restore-bak-file-3-min.png

Step 6: Locate the .bak file, in this example it's AdventureWorks2012.bak stored in D:\, after selecting the file, click "OK"

locate-bak-file-from-pc-4-min.png

Step 7: You will see as image below, Click "Ok"

restore-database-using-bak-file-sql-server-5-min.png

Step 8: We are all done now, database file (.bak) is located and now just need to Click "OK", rest SQL server will handle, so click on "OK" & wait for few seconds until SQL server restores database.

last-step-back-up-database-bak-sql-server-6-min.png

Once done, you will see a pop-up message which shows success message.

done-7-min.png

That's it we are done, you can see the database is restored succesffuly.

database-restored-from-bak-file-sql-server-8-min.png

Another Method in SQL Server is to restore database from bak file using script

In the above method to restore the database in SQL server is to restore the database from bak file using script, so suppose here we have .bak file in D:\, we can run script as below

Using T-SQL

  • Connect to the Database Engine.

  • From the Standard bar, click New Query.

  • In the RESTORE statement, specify a logical or physical backup device to use for the backup operation. This example restores from a disk file that has the physical name

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'D:\AdventureWorks2012.BAK'
GO

the above script, will restore the database using the specified file. If the database already exists it will overwrite the files. If the database does not exist it will create the database and restore the files to same location specified in the backup.

Note: You might get error

Logical file 'AdventureWorks' is not part of database 'AdventureWorks'. Use RESTORE FILELISTONLY to list the logical file names.

OR

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\AdventureWorks.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended)

In this case, it means you are missing .ldf/.mdf files of the above backup, to get it, you need to run the below command

RESTORE FILELISTONLY 
    FROM DISK = 'D:\AdventureWorks2012.BAK'

Once you will execute the above command, you will get location of .mdf/.ldf like "C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf" with logical name "AdventureWorks2012_Data", use it in below query to restore database.

RESTORE DATABASE AdventureWorks2012 FROM DISK = N'E:\AdventureWorks2012.bak'  WITH  FILE = 1, 
 MOVE  N'AdventureWorks2016_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2016_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Log.ldf',
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

Restore a full backup allowing additional restores such as a differential or transaction log backup (NORECOVERY)

The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off.

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'D:\AdventureWorks2012.BAK' WITH NORECOVERY
GO

Restoring a differential database backup

This example restores a database and differential database backup of the MyAdvWorks database.

-- Assume the database is lost, and restore full database,   
-- specifying the original full database backup and NORECOVERY,   
-- which allows subsequent restore operations to proceed.  
RESTORE DATABASE MyAdvWorks  
   FROM MyAdvWorks_1  
   WITH NORECOVERY;  
GO  
-- Now restore the differential database backup, the second backup on   
-- the MyAdvWorks_1 backup device.  
RESTORE DATABASE MyAdvWorks  
   FROM MyAdvWorks_1  
   WITH FILE = 2,  
   RECOVERY;  
GO

You may also like to read:

Download AdventureWorks Sample Database and Restore

How to back up SQL Server database ? (Various Ways explained)