ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

How to Backup a SQL Server 2005 Database

Updated on December 8, 2012

Backing up company data is always a priority, and for just about any organization, no matter the size, at the top of the list are SQL databases. Fortunately, both the 2005 and 2008R2 Microsoft SQL Studio Management Server (SSMS) consoles make backing up your Microsoft SQL databases easier then ever.

In this article I'll walk you through a typical backup, as well as offer up a few tips that might make your life a bit easier down the road (i.e. recovery time).

Ready? Grab that energy drink and let's get started!

  1. Open up your SSMS console and expand the "Databases" tree
  2. Right Click the database you wish to backup, in this case "Howlermunkey Inc."
  3. Select "Tasks"
  4. Select "Backup..."

5. In the backup window, you'll see the default (or last used) location given. If the Radio button is set to "Tape", select "Disk".

TIP - The Microsoft SSMS default location for backups is literally 5 folders deep, and looks something like this: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup ......

IMHO (In My Humble Opinion) - not necessarily the best choice for a few reasons --

a. Takes up space on the C drive, Space that gets lots because its 5 folders deep, so when that C drive starts to fill, and you pull out "Tree Size Free" or "WinDirStat" , you'll have an AHA! moment, there's where 20 gb's is hiding!

b. Do you really want to dig through directories when you have to restore? What if you have BOTH 2005 AND 2008R2, even more directories to hunt through!

Why not consolidate all your backups into one, easy to find directory NOT on the C partition?

6. First click "Remove" to get rid of the old entry, then click "Add" to browse to your directory of choice

7. Hit the little "..." browse tab to the right and choose a directory. In my example, I made a backup directory called "Databases" on my "G" drive. When I need to retrieve a backup, see how easy it will be to find them?

8. IMPORTANT -- Once you have your directory selected, you need to name the backup. Type the name of the file at the bottom where its says "File name". Use something that makes sense, so that you know what you are looking at restore time!

9. Make sure to type the extension .bak at the end of your name, (you have to type the .bak), otherwise, the backup will still work just find, but when the time comes for you to restore, unless you set SSMS to look for "All Files" instead of ".bak", you won't see the files in the directory...

TIP -- Just want to summarize --- Name your backups smartly.... AND Always remember to type ".bak" at the end. Even if its a long name!

For example "Howlermunkey_backup_for_hub.bak"... at least you'll know what you are looking at later on, and can easily find it!

10. Now that you have your directory set, a name that makes sense, and your ".bak" at the end, you're ready to hit the "Add" button.

That's IT! That's not so bad is it? And, if you take just a few minutes to create and use a backup directory that's easy to find, you'll thank yourself down the road!


    0 of 8192 characters used
    Post Comment

    • howlermunkey profile image

      Jeff Boettner 5 years ago from Tampa, FL

      @Lipnancy, sounds like you have some "war" stories to tell lol, would love to hear about 'em :), thanks again for stopping by!

    • Lipnancy profile image

      Nancy Yager 5 years ago from Hamburg, New York

      Oh man you our bringing back old memories of doing backups at work on a system that never worked to begin with.

    • howlermunkey profile image

      Jeff Boettner 5 years ago from Tampa, FL

      Hey hawaiianodysseus, don't drink too many of those bro, you'll be up all night lol! Thanks for the positive comments and stopping by!

      PS - a fine Cabernet (preferably NAPA) can help you "come down" if you've read too many of my hubs lol :)

    • hawaiianodysseus profile image

      Hawaiian Odysseus 5 years ago from Southeast Washington state

      Help! I can't keep up with all the energy drinks! LOL!

      Thanks once again for information that's vital to some of us prolific writers and online entrepreneurs. We need backup information, too!