I Need To Make A Change With My IT Support! Call (225) 706-8414

Backing up SQL 2005/2008 databases to disk

You want to backup SQL Server 2005/2008 databases nightly to disk.

Solution

You need to create two types of jobs within a single task: Back Up Database (Full) and Maintenance Cleanup Task. The Cleanup Task will remove the old backup files–otherwise, you will have more and more backup files.

Keep in mind that backing up a database to the same disk or even the same server carries a high risk. If you lose the disk or server, you will lose the database and the database backup. The backup should go off-server.

  1. Open Microsoft SQL Server Management Studio
  2. Login as a sysadmin
  3. Go to <server>->Management->Maintenance Plans
  4. Right-click Maintenance Plan->Maintenance Plan Wizard
  5. The Plan Properties dialog will open.
  6. For the plan name, choose MaintenancePlan-<SERVERNAME>.
  7. Schedule the job. Choose Change.. in the bottom near “Schedule”
  8. The Job Schedule Properties dialog will open
  9. Set Schedule Type to Recurring
  10. Set Frequency->Occurs to Daily (this may vary on your needs)
  11. Set Recurs Every to 1 (day)
  12. Set Daily Frequency->Occurs once at to the appropriate time, e.g., 3:00:00 AM.
  13. Set Start Date to the earlier time possible, such as today
  14. Click Ok to close the Job Schedule Properties dialog
  15. Click Next to proceed with the Plan Properties dailog
  16. In the Select Maintenance Tasks dialog, choose “Back Up Datqabase (Full)” and “Maintenance Cleanup Task”
  17. Click Next
  18. You’ll be given an option to change the order of the tasks now. Leave the defaults.
  19. Click Next
  20. The Define Back Up Database (Full) Task will open.
  21. For Database(s), choose the appropriate database. The safest option is to choose “All databases”, although that may consume a lot of disk. Click Ok.
  22. Do not modify “Backup set will expire”
  23. Choose “Create a backup file for every database”
  24. Usually you will not choose “Create a sub-directory for each database”
  25. In Folder, select a folder. Using a folder in C: is often dangerous as the backups may fill up the disk. Choose a good location, although the default may work if it’s on D:, etc. One option may be something like D:MSSQL-Backups (assuming you created that directory).
  26. Set Backup file extension to “bak”
  27. Click Next
  28. The Define Maintenance Cleanup Task will open
  29. Set “Backup files” for “Delete files of the following type”
  30. For “File Location”, choose “Search folder and delete files based on an extension”
  31. In Folder, put your backup path, e.g., D:MSSQL-Backups
  32. For File Extension, put “bak”
  33. For File Age->Delete files based on the age.., put in a sane number. If you are backing up D:MSSQL-Backups to tape or offsite, this can be small, perhaps 2 or 3 days. Needs will define this number.
  34. Click Next
  35. Use the defaults for the Report Options
  36. Click Finish at the Complete the Wizard screen
  37. Click Close at the Maintenance Plan Wizard Progress dialog once its done

In general, you’ll want to verify that the Maintenance Plan works! If it’s possible (i.e., you won’t disrupt a production server), right-click the MaintenancePlan-<server> and choose Execute. This will run the job. It may take a few seconds or a long while, depending on the size of all the databases!

atkb#221

 

Concerned About Cyber Attacks?

CLICK HERE >

Want to Migrate to the Cloud?

CLICK HERE >
Office 365

Ready to Experience Microsoft Office 365?

Want the latest IT news directly in your inbox? Subscribe now!