MS SQL Server - Restoring Databases
Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database.
The Restore database option can be done using either of the following two methods.
Method 1 – T-SQL
Restore database <Your database name> from disk = '<Backup file location + file name>'
The following command is used to restore database called 'TestDB' with backup file name 'TestDB_Full.bak' which is available in 'D:\' location if you are overwriting the existed database.
Restore database TestDB from disk = ' D:\TestDB_Full.bak' with replace
If you are creating a new database with this restore command and there is no similar path of data, log files in target server, then use move option like the following command.
Make sure the D:\Data path exists as used in the following command for data and log files.
RESTORE DATABASE TestDB FROM DISK = 'D:\ TestDB_Full.bak' WITH MOVE 'TestDB' TO 'D:\Data\TestDB.mdf', MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'
Method 2 – SSMS (SQL SERVER Management Studio)
Step 1 − Connect to database instance named 'TESTINSTANCE' and right-click on databases folder. Click Restore database as shown in the following snapshot.
Step 2 − Select device radio button and click on ellipse to select the backup file as shown in the following snapshot.
Step 3 − Click OK and the following screen pops up.
Step 4 − Select Files option which is on the top left corner as shown in the following snapshot.
Step 5 − Select Options which is on the top left corner and click OK to restore 'TestDB' database as shown in the following snapshot.