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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

10 comments:

Anonymous said...: Awesome,
very good instructions, thanks much; November 26, 2008 at 9:22 AM
Unknown said...: This comment has been removed by the author.; December 16, 2008 at 10:42 AM
Unknown said...: Good instructions and script works wonderfully but it’s missing a ' (single quote) in the end of the both MOVE commands.

Also, I get an error message in both of the MOVE commands saying it can’t find the Data and the Log file in my .bak file.

I removed the both of the MOVE command and ran the script and it worked and creating a new database and loaded all the data. It also created the mdf and the ldf file in the end as well. Do we need the MOVE commands and what they suppose to do? Just curious!!!; December 16, 2008 at 10:47 AM
Thanks said...: Just what I was looking for thanks!; August 21, 2009 at 3:34 AM
Anonymous said...: Thank you, this was perfect.; September 28, 2009 at 12:32 PM
Felix Ivan Romero Rodríguez said...: I have a problem, when i restore my db, it is marked as single user, how i can quit this.
thanks; November 18, 2009 at 6:23 AM
Anonymous said...: I got a good way with every step screen shot for creating backup and restore database using sql server 2008. here is the link
Thanks; January 26, 2011 at 11:51 AM
YOGRAJ said...: x64 mssql backup i want restore in x32 bit version is 2005; March 21, 2011 at 3:13 AM
sanket asrudkar said...: thank you...; July 25, 2011 at 12:59 AM
Unknown said...: Thank You...; November 23, 2012 at 11:33 PM