Archive for the ‘SQL Server 2005’ Category

Restore SQL Server 2005 backup file (*.bak)

Wednesday, July 29th, 2009

This tutorial is intended for restoring backup in Microsoft SQL Server 2005 by using Microsoft SQL Server Management Studio Express. Some of you might have tried tweaking around using the GUI of SQL Server Management Studio to restore the backup file. Most of junior developer will face this error:

Error 3154: The backup set holds a backup of a database other than the existing database.

Ok, let’s get into our business. First of all get the backup file (*.bak) and the tool (Microsoft SQL Server Management Studio Express) ready.

You should create the database (with the same file name *.mdf & *.ldf) which has the same name to your backup file.
The easiest way to do this, is try to open your backup file and figure out what is the database name, database file name ( ???.MDF), and log file name (???.LDF).  On your Object Explorer right click the “Databases”, choose “Restore Database”,  select “From Device”, and browse your *.bak file.

Once you get that, create new database with the same Database name and  change the Logical name accordingly.

Once the database has been setup run the following query:

RESTORE DATABASE YOUR_DATABASE_NAME
FROM DISK = ‘C:\PATH_TO_YOUR_BACKUP\YOUR_BACKUP_FILE.bak
WITH REPLACE