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.

at

10 comments:

  1. AnonymousNovember 26, 2008 at 9:22 AM

    Awesome,
    very good instructions, thanks much

    ReplyDelete
  2. UnknownDecember 16, 2008 at 10:42 AM

    This comment has been removed by the author.

    ReplyDelete
  3. UnknownDecember 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!!!

    ReplyDelete
  4. ThanksAugust 21, 2009 at 3:34 AM

    Just what I was looking for thanks!

    ReplyDelete
  5. AnonymousSeptember 28, 2009 at 12:32 PM

    Thank you, this was perfect.

    ReplyDelete
  6. Felix Ivan Romero RodríguezNovember 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

    ReplyDelete
  7. AnonymousJanuary 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

    ReplyDelete
  8. YOGRAJMarch 21, 2011 at 3:13 AM

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

    ReplyDelete
  9. sanket asrudkarJuly 25, 2011 at 12:59 AM

    thank you...

    ReplyDelete
  10. UnknownNovember 23, 2012 at 11:33 PM

    Thank You...

    ReplyDelete