There are many ways to restore MSSQL Server database from backup files(.bak) like using tools available in SQLServer Management tools. But we face problems when the backup was not created on the same machine or it was shared by someone else or it was created in older version. This blog may help you in making this process hassle free.

First thing we need to know is the logical name of mdf and ldf files of the original database. To get those open a new query window in SQLServer Management Studio and type the following command :

RESTORE FILELISTONLY FROM DISK='D:\Backups\DBBackup.bak';

Where Backups should be replaced by the folder location of your backup file and DBBackup should be replaced by name of your backup file.

The output will be similar to :

The values of our interest are in the column named LogicalName the first value in that column is the logical name of the mdf file and second is the logical name of ldf file

Now use the following command :

RESTORE DATABASE DBName
FROM DISK='D:\Backups\DBBackup.bak' WITH REPLACE,
MOVE 'magazinedb' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBName.mdf',
MOVE 'magazinedb_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBName.ldf';

Here DBName should be replaced by the name of the database to be restored, magazinedb should be replaced by the logical name of your mdf file and magazinedb_log should be replaced by the logical name of your ldf file. Both the file names we have from the first command.

On successful restoration the output will be similar :