Go Back   AnandTech Forums > Software > Operating Systems

Forums
· Hardware and Technology
· CPUs and Overclocking
· Motherboards
· Video Cards and Graphics
· Memory and Storage
· Power Supplies
· Cases & Cooling
· SFF, Notebooks, Pre-Built/Barebones PCs
· Networking
· Peripherals
· General Hardware
· Highly Technical
· Computer Help
· Home Theater PCs
· Consumer Electronics
· Digital and Video Cameras
· Mobile Devices & Gadgets
· Audio/Video & Home Theater
· Software
· Software for Windows
· All Things Apple
· *nix Software
· Operating Systems
· Programming
· PC Gaming
· Console Gaming
· Distributed Computing
· Security
· Social
· Off Topic
· Politics and News
· Discussion Club
· Love and Relationships
· The Garage
· Health and Fitness
· Home and Garden
· Merchandise and Shopping
· For Sale/Trade
· Hot Deals with Free Stuff/Contests
· Black Friday 2014
· Forum Issues
· Technical Forum Issues
· Personal Forum Issues
· Suggestion Box
· Moderator Resources
· Moderator Discussions
   

Reply
 
Thread Tools
Old 11-28-2012, 09:17 AM   #1
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default Backing up SQL Server

Not sure if this is the best forum to post this or if it should be in software or Programming or what...

Regardless, what is the preferred method to back up data in a MSSQL server? I realize I could easily just backup all the .mdb files themselves, but I imagine there is a more eloquent and "proper" way to backup the information on a nightly basis so that it can more easily/properly be restored in the event of an issue.

Thanks in advance for any advice.
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 11-28-2012, 09:56 AM   #2
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

For free: Load up the management tools, right click the database and hit backup. Just backing up the .mdb files may not give you a consistent database. You can also throw together a powershell script to do it for you also.

For $: A dedicated app like BackupExec that can do row and table restores.

You need to make sure the database is consistent prior to backup. Typically the backup commands cause SQL to roll anything in memory pending writes in to the database or db logs, then save a copy. This of course varies on your implementation. If you are doing log shipping etc it gets quite complex.
imagoon is offline   Reply With Quote
Old 11-28-2012, 09:59 AM   #3
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

Quote:
Originally Posted by imagoon View Post
For free: Load up the management tools, right click the database and hit backup. Just backing up the .mdb files may not give you a consistent database. You can also throw together a powershell script to do it for you also.

For $: A dedicated app like BackupExec that can do row and table restores.

You need to make sure the database is consistent prior to backup. Typically the backup commands cause SQL to roll anything in memory pending writes in to the database or db logs, then save a copy. This of course varies on your implementation. If you are doing log shipping etc it gets quite complex.
I'm familiar with doing a backup via SSMS, or even a T-SQL script. I assume then if I went that route and sent the backup to Z: logical drive, I could then instruct our Network Admin to simply back up Z: nightly to tape (along with everything else that is being backed up)
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 11-28-2012, 10:04 AM   #4
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

Quote:
Originally Posted by Homerboy View Post
I'm familiar with doing a backup via SSMS, or even a T-SQL script. I assume then if I went that route and sent the backup to Z: logical drive, I could then instruct our Network Admin to simply back up Z: nightly to tape (along with everything else that is being backed up)
Scripting a backup dump to a backed up folder would work to protect the data. It really depends on how far you need to go. A dump won't [easily] give you row restores and the like. If you don't need that then you are likely fine. On small "not that important" setups I have done a scheduled dump to a local disk and then backed up the entire machine. If I cared a bit more I would schedule the dumps and use the current date as part of the file name and dump them off to another server like you mentioned. If the DB was business critical it got a replica + Backupexec style backups.
imagoon is offline   Reply With Quote
Old 11-28-2012, 10:08 AM   #5
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

Quote:
Originally Posted by imagoon View Post
Scripting a backup dump to a backed up folder would work to protect the data. It really depends on how far you need to go. A dump won't [easily] give you row restores and the like. If you don't need that then you are likely fine. On small "not that important" setups I have done a scheduled dump to a local disk and then backed up the entire machine. If I cared a bit more I would schedule the dumps and use the current date as part of the file name and dump them off to another server like you mentioned. If the DB was business critical it got a replica + Backupexec style backups.
Yeah. It's fairly important stuff (work databases).
I think I will mess with a scheduled dump to Z:, have Z: backed up to tape nightly. Then if anything happens, I can restore from those nightly backups.

Though I could just recommend Backup Exec take care of it all, then it's out of my responsibility
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 11-28-2012, 11:46 AM   #6
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

Yup, then your not on the hook if your "homebrew" fails for some reason and the company loses "MILLIONS!"
imagoon is offline   Reply With Quote
Old 11-28-2012, 12:21 PM   #7
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

Quote:
Originally Posted by imagoon View Post
Yup, then your not on the hook if your "homebrew" fails for some reason and the company loses "MILLIONS!"
EXACTLY!!!

Whats "funny" (it's not really funny) is that I asked our Admin some time again "is everything being backed up?" I was told "Yes" (verbatim)

Well my boss asked me yesterday if it was being backed up... I explained "I was told 'Yes'... exactly that. Nothing more." She then went and got more details. Which when the details were relayed to me, it was clear the DBs were NOT being backedup at all.

So now, it's in my lap to get it done right
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 11-28-2012, 12:38 PM   #8
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

Heh... I don't believe people that have short 'yes/no' answers to 'complex questions.' Is it backed up should not be just 'yes' as an answer, esp if the person responding spends 0 time researching.
imagoon is offline   Reply With Quote
Old 11-28-2012, 03:33 PM   #9
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

http://ola.hallengren.com/sql-server-backup.html

interesting...
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 11-28-2012, 04:21 PM   #10
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

Looks like it just wraps the built in functions for people that don't know how to build the SQL scripts. This isn't bad thing btw but as a sysadmin I would likely take the powershell, system style rather than internal SQL based but that is because I am not a DBA.
imagoon is offline   Reply With Quote
Old 11-28-2012, 04:32 PM   #11
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

Quote:
Originally Posted by imagoon View Post
Looks like it just wraps the built in functions for people that don't know how to build the SQL scripts. This isn't bad thing btw but as a sysadmin I would likely take the powershell, system style rather than internal SQL based but that is because I am not a DBA.
The scripts are quite easy to create actually. I already technically have what I need functioning. I don't need to get overly complicated, but using that linked script as a "learning tool" helps quite a bit. I can pick it apart and learn from it quite a bit.
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 12-03-2012, 09:30 AM   #12
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

heh. After all that, it looks like they bought the SQL backup addin for Backup Exec anyways.

Oh well. Easier for them to spend the $$ I guess than to just have me create a quick script
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 12-03-2012, 10:22 AM   #13
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

Quote:
Originally Posted by Homerboy View Post
heh. After all that, it looks like they bought the SQL backup addin for Backup Exec anyways.

Oh well. Easier for them to spend the $$ I guess than to just have me create a quick script
Well, you have to look at it from their side:

We can buy app X that has a large following, support is available and there in instructions all over the web.

-or-

We can have Homerboy hack together something that has no independent testing and no real proof that it "works" and if he gets beer trucked next week, the next guy may have no idea / clue what he did.

To a company, all of a sudden the $1200 license looks cheap.
imagoon is offline   Reply With Quote
Old 12-03-2012, 11:15 AM   #14
Homerboy
Lifer
 
Homerboy's Avatar
 
Join Date: Mar 2000
Location: MKE, WI
Posts: 19,793
Default

You mentioned a beer truck....
__________________
"Ah... In a time of such ugliness, the only true protest is to be beautiful." - Refused
Homerboy is offline   Reply With Quote
Old 12-03-2012, 12:39 PM   #15
uallas5
Senior Member
 
uallas5's Avatar
 
Join Date: Jun 2005
Posts: 331
Default

What version of MSSQL are you running? We have some 2003 installs here and I've found SQL backup and ftp to be very effective: http://sqlbackupandftp.com/.

2003 has no internal compression to it's backup routine so this is a useful solution for my off site backups. I've only used it with ftp'ing an encrypted compressed file, so I cannot comment about it's internet backup abilities.
__________________
"I Know Bad From Good and Where to Get the Refill" - Mr. Harding, The Schizophrenic Stock Broker

"The Problem With the World is That Everyone is a Few Drinks Behind" - Humphrey Bogart
uallas5 is offline   Reply With Quote
Old 12-03-2012, 05:09 PM   #16
imagoon
Diamond Member
 
imagoon's Avatar
 
Join Date: Feb 2003
Location: Chicagoland, IL
Posts: 4,901
Default

Quote:
Originally Posted by Homerboy View Post
You mentioned a beer truck....
My colorful way of saying: "HomerBoy is a blood stain on the highway / in a box / currently held prisoner in the middle of North Korea / Tell your boss to get lost etc"

IE what happens when your IT guy vanishes.
imagoon is offline   Reply With Quote
Old 12-03-2012, 09:19 PM   #17
Nothinman
Elite Member
 
Nothinman's Avatar
 
Join Date: Sep 2001
Posts: 30,672
Default

Quote:
Originally Posted by Homerboy View Post
heh. After all that, it looks like they bought the SQL backup addin for Backup Exec anyways.

Oh well. Easier for them to spend the $$ I guess than to just have me create a quick script
Good luck with that, the one time I had to do a SQL 2005 restore via BE it took hours to do a relatively small DB of less than 1G.

Depending on the software and your restoration requirements, you probably want to at least put the DB into full logging mode so that you can do point in time restores. Based upon my experience, and hatred of BackupExec for other reasons, I would still go the scheduled task routine of dumping the database and its logs to a directory that's picked up by BE. I would bet the additional flexibility (and likely reliability compared to BE) will be useful should you ever need to do a restore. If nothing else, make sure you test your restore procedure periodically to make sure it still works as expected.
__________________
http://www.debian.org
Nothinman is offline   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 09:28 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.