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.


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

11 comments:

Anonymous said...

Awesome,
very good instructions, thanks much

talwar@gci.net said...
This comment has been removed by the author.
talwar@gci.net 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!!!

Thanks said...

Just what I was looking for thanks!

Anonymous said...

Thank you, this was perfect.

Felix said...

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

Alex said...

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.

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

Attitude said...

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

sanket asrudkar said...

thank you...

Mordhwaj Chauhan said...

Thank You...