Restore backup of database (.bak) using Sql Server 2005

If you intend to create a back up of your database in Server A and restore it on a Server B, here’s how to go about it. Create a .bak file (Open SSMS > Database > Right Click your database > Tasks > BackUp > Specify the destination).

To do it using script, check my blog over here.

Once the .bak is created, copy this file from Server A to a Server B. We will assume that the file has been copied at the location ”C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\” at Server B. It can any location that you desire.

How to restore the .bak file

Use this query:

RESTORE DATABASE [SouthWind]
FROM DISK = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\South.BAK’
WITH REPLACE,
MOVE ‘SouthWind_Data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Data.MDF’,
MOVE ‘SouthWind_Log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Log.LDF’

where FromDisk is where the .bak file is kept on Server B and MOVE specifies the location of the data and log file.

http://www.sqlservercurry.com/2008/02/restore-bak-using-sql-server-2005.html