How to back up a MS SQL Server 2005 Database?

If you use a database to store your data then it becomes mandatory to backup it up. A Backup is a copy of your data that is maintained so that you can bring back your data, in case of a system failure. Depending on how important your data is you need to back it up every day/ every week or every month. The following article explains how to backup your MS SQL DB.

1. Click Start > All Programs > MS SQL Server 2005 > SQL Server Management Studio. The SQL Server Management Studio will now be launched.
2. Enter your login details and connect to the server.
3. Expand the Databases folder and select the DB that you want to backup. Right-click this database and select Tasks > Back Up… option. The Back Up Database window will be launched.

4. From the Backup Type drop down select the Full option to perform a complete database backup.
5. In the Backup Component section select the Database option.
6. Enter a name for the backup in the Name box. Enter a short description about the Backup in the Description box.
7. Click the Add button to specify a destination for the backup.

8. Click the Options tab in the left pane. The options will now be displayed.
9. Select the Append to the existing backup set option to append the current backup to existing file or device.
10. Check the Verify backup upon completion option to verify the backup upon completion.

    

11. Click the OK button to begin the Backup. Once the backup is complete you will get a confirmation message, click OK.
12. Now, again right-click the DB from the list and select Tasks > Back Up… option. The Back Up Database window will be launched.
13. Select the Transaction Log option to backup the DB’s log from the Backup Type drop down.
14. Enter a name for the backup in the Name box. Enter a short description about the backup in the Description box.
15. Click the Add button to specify a destination for the backup.
16. Click the Options tab in the left pane. The options will now be displayed.
17. Select the Append to the existing backup set option to append the current backup to existing file or device.
18. Check the Verify backup upon completion option to verify the backup upon completion.
19. Click the OK button to begin the Backup. Once the backup is complete you will get a confirmation message, click OK.
20. Close the SQL Server Management Studio.
21. Now, copy these files to a device (Pen Drive) or burn them on a CD.

For more info on backup see, MS SQL Server 2005 Backup