ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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 imageAUTHOR

      Jeff Boettner 

      6 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 

      6 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 imageAUTHOR

      Jeff Boettner 

      6 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 

      6 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!



    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)