Backing up SQL Server

Homerboy · 11-28-2012, 08:17 AM

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.

imagoon · 11-28-2012, 08:56 AM

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.

Homerboy · 11-28-2012, 08:59 AM

Quote:

Originally Posted by imagoon

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)

imagoon · 11-28-2012, 09:04 AM

Quote:

Originally Posted by Homerboy

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.

Homerboy · 11-28-2012, 09:08 AM

Quote:

Originally Posted by imagoon

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

imagoon · 11-28-2012, 10:46 AM

Yup, then your not on the hook if your "homebrew" fails for some reason and the company loses "MILLIONS!"

Homerboy · 11-28-2012, 11:21 AM

Quote:

Originally Posted by imagoon

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

imagoon · 11-28-2012, 11:38 AM

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.

Homerboy · 11-28-2012, 02:33 PM

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

interesting...

imagoon · 11-28-2012, 03:21 PM

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.

Homerboy · 11-28-2012, 03:32 PM

Quote:

Originally Posted by imagoon

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.

Homerboy · 12-03-2012, 08:30 AM

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

imagoon · 12-03-2012, 09:22 AM

Quote:

Originally Posted by Homerboy

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.

Homerboy · 12-03-2012, 10:15 AM

You mentioned a beer truck....

uallas5 · 12-03-2012, 11:39 AM

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.

imagoon · 12-03-2012, 04:09 PM

Quote:

Originally Posted by Homerboy

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.

Nothinman · 12-03-2012, 08:19 PM

Quote:

Originally Posted by Homerboy

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.