Methods To Restore SQL Database From Backup File

To restore either a full or differential backup you need to use the RESTORE DATABASE option. In the present article we will take a look at the steps that you need to follow to restore a SQL Server Database from a Full Database Backup. The steps mentioned in this article are applicable to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

Permission to Restore SQL Server Database From Backup File

To restore a database from backup file user must have DBCREATOR or DB_OWNER role else they will receive the SQL Server Error 15105 while restoring a database in SQL Server.

Operating System Error 5: “5(failed to retrieve the text. Reason: 15105)”
You may also identify the error 15105 like this: Cannot open backup device “\\xxxxxx\xxx.bak”.

Restore SQL Database From Backup File Using SSMS

You need to follow the steps given below to restore database from backup file with the help of SQL Server Management Studio (SSMS).

1. Firstly, open the SQL Server Management Studio > Connect to the SQL Server database (via Windows Authentication or SQL Server SA Authentication).

2. Once you connected, right click on the Databases folder and select Restore Database.

3. Select From device under the Source for restore section.

4. Select File as the backup media and then click Add.

5. Browse the SQL Server backup file (.bak) which you want to restore.

6. Now specify the backup type and then click on OK.

7. In the Restore Database dialog box, select the name of the database in which you want this backup to be restored.

8. Select the restore option as per your requirement.

9. Once the restore is completed, click OK.

Restore SQL Database From Backup File Using Transact-SQL

Let’s see, how we can restore a SQL Server database from backup file with the help of T-SQL query. The end user can restore the database beside DB Creator or System Administrator.

There are certain options to restore the backup file, you can use one as per your convenience. Here, I have discussed all the options.

1. This command will restore database using the specified file. If the database already exists, it will overwrite the files.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\employee.bak'
    GO

2. The NORECOVERY option leaves the database in a restoring state after the restoration will completed.

RESTORE DATABASE employee.bak FROM DISK = 'C:\employee.bak'
    WITH NORECOVERY
    GO

3. While restoring the differential backup, the first thing you should do is perform a full restore using the NORECOVERY option.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\ employee.bak' WITH NORECOVERY
    GO
    RESTORE DATABASE AdventureWorks FROM DISK = 'C:\ employee.bak'
    GO

4. To restore a backup file that has multiple backups, you can use RESTORE HEADER ONLY.

RESTORE DATABASE employee.bak FROM DISK = 'C:\employee.bak' WITH NO RECOVERY, FILE 1
    GO
    RESTORE DATABASE employee.bak FROM DISK = 'C:\employee.bak' WITH FILE 2
    GO

Conclusion

In the present article, we have discussed about how to restore SQL database from backup file using the SQL Server Management Studio and Transact-SQL. To perform the restore operation you must have the DB creator authority or you must have the System Administrator role.