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 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, stpe 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 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 don'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 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 clikc 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 to restore database from bak file using script

In the above method to restore database in sql server is to restore 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.

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