SQL Backup

[clytwyn]

Good morning,

I'm a total rookie when it comes to SQL. But I've been assigned the task of backing it up. After talking to many people and searching on websites I've gotten lots of suggestions. I'm unsure which may be the best solution for our company. As of right now I'm leaning towards this solution. I was told to do a backup of the database to a virtual device/folder and then back that dump file up onto tape either using Veritas or the microsoft backup software. And I should also backup the transaction logs. and if I should need to restore the backup then I would restore the backup to the virtual device/folder and then import that dump file into the database. What I don't understand is why not just backup the database to tape and call it a day. Why all the extra work with the virtual device? I also own Veritas 9.1 with the sql open agent. But I was told it was a hog on resources. All though are backup would only be run at night so not really an issue.

Thanks for your responses. Much appreciated.

 

[donutman]

Your solution sounds perfectly reasonable for your situation where presumably nothing much is going on at night. If in addition, you database isn't very big and I'm betting it isn't because they have entrusted a "total rookie" (as you say) to the task, then consider using my technique that bypasses the tape nuisance.
Use EM to create a maintenance job that does an entire backup and truncates the log files each night. Have it keep the backups for about 10 days. Share the folder that is used to save the backups. Create a scheduled job on a computer that is left on over night that copies any new files from that folder to its hard-drive. Write a program to delete the old files, but keeping "special" backups for a longer time, like 1st of each week, month and year.
This makes the job completely automated. Just be sure to check on it from time to time...or have the application verify the existence and reasonableness of the files created!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

Backups to tape are a very important piece of any good backup and restore stragity. The reason is that you should take a setup of backups off site every week or month, as a just in case measure. Some companies will store these backups in a protective vault like Iron Mountain. Others simply buy a fire proof safe and put it in the Admin's home.

Never use the built in Microsoft Backup utility to backup any server. It is a piece of crap and rarly works correctly. I have heard stories of people using the built in Microsoft backup to backup there servers to tape, then when they needed to restore the tapes were actually blank, even though the Microsoft backup said that the backups were done with 0 errors.

A good backup stragety involves backups to both tape and disk, with a good rotation and retention policy. If you would like I can go into more detail, just let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[nigelrivett]

>> Why all the extra work with the virtual device?
Because it's safer. You also have a backup on disk which you can restore quickly if you need to.
Avoid the maintenance plan exe.
Only backup logs if you need to (if it's a business requirement) otherwise set the recovery model to simple and ignore them.

Important.
Restore from your backups to test them. For important databases I do a test restore and dbcc check (automatically) of every backup
see

http://www.nigelrivett.net/s_TestRestore.html

It puts the backup and data files into the same directory - I'm going to change it soon to allow it to specify the data and log directories separately.

Other backups test periodically.
Also periodically get a tape back and test the restore from it. This tests whether the tapes are really being kept like you think they are and that the database is restorable.
I've had a few problems with this - one where the company thought doing a test restore from tape meant they copied a backup to a tape and gave it to me - they weren't actually copying anything to tape otherwise.
Another one they were taking the tapes offsite daily then bringing them back again and overwriting with the next backup which had the same effect.
Another where these were recycled weekly - useless for the backups that were onl;y taken weekly - and the monthlies which were meant to be kept forever.
Don't assume just because you have (or had) an agreement with tape storage that it actually happens nor that it will contine to happen.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

I certainly can't argue with the thoroughness of mrdenny and nigelrivett's approach. For my small shop I've found the testing of a tape backup very annoying, but absolutely necessary if you use tape. Because I'm in business for myself I don't like spending the money on tapes and pricey backup software, the hard-drive approach works great and unlike tape it has never failed a test. However, I have to admit that I've failed to anticipate the major disaster of losing the server and my workstation in a coincident event. That's easy to fix: I'll just schedule a job to make an off-site copy of my generation backups...we have T1's connecting our locations AND my home!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[clytwyn]

Thank you all very much for you input. I greatly appreciate the assistance. From what all of you have said I think I know the direction I'll go for sure. Once again thank you.