backup and restore database on Microsoft SQL Server 2005

How to backup and restore database on Microsoft SQL Server 2005

MS SQL Server, Windows January 12th, 2008

Introduction

This post will give you a brief introduction of SQL Server backup. As we know, SQL Server backup software is springing up nowadays. Take EaseUS Todo Backup Advanced Server as an example, it’s an all-in-one SQL server backup software for MS SQL Server 2000/2005/2008, exchange server and server 2000/2003/2008, and provides proven database backup solution for Server administrators. You can Download Free Trial before you decide to buy.

 

But in the example below, I will show a step-by-step database backup from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition using Microsoft SQL Server Management Studio.

Step-by-step

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…
   
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.

   

4. Wait for a while and you’ll see a pop-up message when backup is finished.
   
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”.
   

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.
   
2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
3. Right-click on Databases. Select Restore Database…
   
4. Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.
5. On Specify Backup, ensure that Backup media is “File” and click Add.
   
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.
   
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.

   

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