Friday 4 July 2014

Manually Restore an MSSQL Database in SQL Management Studio

Hi Everyone,

If you need to manually restore an SQL Database in Management Studio you can follow the instructions below for the version of SQL Server running on your server.

SQL 2012:
  1. Log onto SQL Server Management Studio.
  2. Navigate to the database you wish to restore.
  3. Right Click > Tasks > Restore > Database.
  4. Once on the General tab set the source to Device > Select Backup Device > Add > Navigate to the .bak file you're restoring to.
  5. Ensure The Destination Database is the correct one.
  6. Select the Options tab.
  7. Check "Overwrite the existing database (WITH REPLACE)" and "Close existing connections to destination database".
  8. Uncheck "Take tail-log backup before restore".
  9. Click OK.
Why we uncheck the Take tail-log Backup Before restore??
Tail-log backups are new to SQL Server 2005. The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you’re going to restore a database to the point of failure, then you need to take a tail-log backup before you start the restore operation.
If you’re going to restore to a point in time prior to the last transaction log backup, if you’re moving the database from one server instance to another, or if you’re overwriting the existing database, then you won’t need a tail-log backup. If the transaction log is damaged and you can’t take a tail-log backup, then you must restore without one.
SQL 2008:
  1. Log into SQL Server Management Studio.
  2. Navigate to the database you wish to restore.
  3. Right click > Tasks > Restore > Database.
  4. Once on the General tab select "From Device" for the source for restore, then navigate to the .bak file you're restoring to.
  5. Ensure the Destination for restore is set to the correct database.
  6. Select the Options tab.
  7. Check "Overwrite the existing database (WITH REPLACE)"
  8. Click OK