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"
Step 4: Now select the "Device" radio button and then click on browse(...) button to locate the .bak file stored in your hard drive.
Step 5: As soon as you will click browser(...) but, a dialog box like below will appear, click "Add" inside that dialog box
Step 6: Locate the .bak file, in this example it's AdventureWorks2012.bak stored in D:\, after selecting the file, click "OK"
Step 7: You will see as image below, Click "Ok"
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.
Once done, you will see a pop-up message which shows success message.
That's it we are done, you can see the database is restored succesffuly.
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
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.
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
-- 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)