- HubPages»
- Technology»
- Computers & Software»
- Computer Software
How to Backup a SQL Server 2005 Database
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!
- Open up your SSMS console and expand the "Databases" tree
- Right Click the database you wish to backup, in this case "Howlermunkey Inc."
- Select "Tasks"
- 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!
Some more of howlermunkey's Awesome reads:
