February 28, 2008

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

11 Responses to "Restore backup of database (.bak) using Sql Server 2005"
  1. Anonymous said...
    November 26, 2008 at 9:22 AM

    Awesome,
    very good instructions, thanks much

  2. talwar@gci.net said...
    December 16, 2008 at 10:42 AM
    This comment has been removed by the author.
  3. talwar@gci.net said...
    December 16, 2008 at 10:47 AM

    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!!!

  4. Thanks said...
    August 21, 2009 at 3:34 AM

    Just what I was looking for thanks!

  5. Anonymous said...
    September 28, 2009 at 12:32 PM

    Thank you, this was perfect.

  6. Felix said...
    November 18, 2009 at 6:23 AM

    I have a problem, when i restore my db, it is marked as single user, how i can quit this.
    thanks

  7. Alex said...
    April 18, 2010 at 12:23 PM

    I seldom work with sql server and files too. But once I couldn't use my files,because of they were corrupted. And fortunately I incidentally entered in the Internet and observed there - sql server repair database. The utility resolved my issue for a minute and free of charge as I bore in mind.

  8. Anonymous said...
    January 26, 2011 at 11:51 AM

    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

  9. Attitude said...
    March 21, 2011 at 3:13 AM

    x64 mssql backup i want restore in x32 bit version is 2005

  10. sanket asrudkar said...
    July 25, 2011 at 12:59 AM

    thank you...

  11. Mordhwaj Chauhan said...
    November 23, 2012 at 11:33 PM

    Thank You...

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions