How to backup and restore database on Microsoft SQL Server 2005

Backup and restore database on Microsoft SQL Server 2005

This post will shows a step-by-step guide to backup and restore a database between two Microsoft SQL Server 2005 instances. By using backup, you can backup a database without interrupt any transactions on the database.

In the example below, I will try to backup a database from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition.

Backup a database

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

  1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
  2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
    Backup a SQL Server database
  3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
    1. Database – a database that you want to backup.
    2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
    3. Name – Name of this backup, you can name anything as you want.
    4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    5. Click OK to proceed backup.

    Select source and destination to backup

  4. Wait for a while and you’ll see a pop-up message when backup is finished.
    Backup success pop-up message
  5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
    The backup file from SQL Server 2005 Server

Restore the database

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

  1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
    The backup file
  2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
  3. Right-click on Databases. Select Restore Database…
    Restore the database
  4. Restore Database window appears. On Source for restore, select From device and click […] buttton to browse file.Select source device
  5. On Specify Backup, ensure that Backup media is “File” and click Add.
    Select the backup media to restore
  6. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
    Select the backup file to restore
  7. Back to Restore Database window.
    1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
    2. On Source for restore, check the box in front of the backup name (in Restore column).
    3. Click OK.

    Select a destination database to restore

  8. Wait until restore finish and there’ll be a pop-up message notify.
    Restore success pop-up message
  9. Now you’ll see the restored database on the destination SQL Server.
    The restored database on SQL Server 2005

89 Comments

  1. Blake Brannon January 15, 2008
  2. linglom January 15, 2008
  3. priya January 17, 2008
  4. linglom January 17, 2008
  5. Lance May 29, 2008
  6. linglom May 30, 2008
  7. Mal October 20, 2008
  8. ranjith December 24, 2008
  9. linglom December 24, 2008
  10. dfunky January 27, 2009
  11. linglom January 27, 2009
  12. Khaja January 28, 2009
  13. safa March 9, 2009
  14. linglom March 18, 2009
  15. sandeep June 6, 2009
  16. sandeep June 7, 2009
  17. aruna June 7, 2009
  18. pammi June 8, 2009
  19. linglom June 18, 2009
  20. aruna June 19, 2009
  21. Pankaj July 1, 2009
  22. Pankaj July 1, 2009
  23. linglom July 2, 2009
  24. vinod August 6, 2009
  25. linglom August 11, 2009
  26. RLM September 10, 2009
  27. aruna September 23, 2009
  28. linglom September 25, 2009
  29. Kiran Khan October 8, 2009
  30. linglom October 13, 2009
  31. Sulochana October 22, 2009
  32. Marcus November 6, 2009
  33. linglom November 9, 2009
  34. Inkus November 17, 2009
  35. linglom November 17, 2009
  36. Inkus November 17, 2009
  37. vandana December 5, 2009
  38. M Border January 7, 2010
  39. linglom January 7, 2010
  40. Mary Border January 7, 2010
  41. Alex Rigg February 6, 2010
  42. marina February 19, 2010
  43. linglom February 23, 2010
  44. DM Choudhari February 24, 2010
  45. linglom February 26, 2010
  46. ravi kore March 18, 2010
  47. Alex Rigg March 22, 2010
  48. Mahesh Tryambake April 20, 2010
  49. Mukky April 22, 2010
  50. madan July 3, 2010
  51. linglom July 15, 2010
  52. nandakumar September 28, 2010
  53. Puneet October 9, 2010
  54. linglom October 11, 2010
  55. fibrecode November 11, 2010
  56. linglom November 12, 2010
  57. fibrecode November 12, 2010
  58. fibrecode November 12, 2010
  59. harikumar November 19, 2010
  60. Janet November 25, 2010
  61. John December 11, 2010
  62. linglom December 18, 2010
  63. Tony January 7, 2011
  64. dilip kumar January 14, 2011
  65. HOSAIN January 27, 2011
  66. HOSAIN January 27, 2011
  67. Manisha February 15, 2011
  68. jay February 24, 2011
  69. jayesh March 3, 2011
  70. jay March 6, 2011
  71. ahmed March 7, 2011
  72. anth0ny989 March 17, 2011
  73. linglom March 31, 2011
  74. Shukla Ajay April 13, 2011
  75. Maninder April 28, 2011
  76. Brian May 25, 2011
  77. suhas September 15, 2011
  78. linglom September 19, 2011
  79. Purushothaman S October 13, 2011
  80. satish January 12, 2012
  81. Rupali April 5, 2012
  82. Kudeep April 30, 2012
  83. Rupali April 30, 2012
  84. khoa bax July 28, 2012
  85. Bhosz October 8, 2012
  86. Ramanan October 18, 2012
  87. Prashanth November 30, 2012
  88. Syed Jafar Ali Shah December 21, 2012

Leave a Reply

%d bloggers like this: