How does everyone backup? 2

[Will192]

Well we had the C drive of our main server become corrupt over the weekend. Nothing like a disaster to bring to lite the inadequcies of the backup plans. I figure that I would start a topic of what else do I need to be safe from data loss. Here's my current list of activities that we are/will be doing to recover from emergencies. I would appreciate anyone that has any other reccommendations to please post them. We can take the loss of up to 24 hours of transactions, hence the nightly backup. We will probably be decreasing that time after we get a better backup/recovery plan in place.

Nightly :
dump of all databases
Zip all dumps and transfer off server
BCP dump of all data tables
Zip all BCP dumps and transfer off server
Zip all external sql code(text files) and transfer off server

Weekly
Burn copies of previous nights zipped files and take off site
Script all databases and jobs and burn to CD and take off site

This is all that we are doing right now. We have found that it's easier to transfer the zip files to a different machine than to dump them to tape. How does everyone on this forum handle backing up the boot disk drive?

 

[Will192]

Didn't put in database sizes. Sorry about that. Pulled an all day & nighter Sunday to Monday to get system back up at 6am.

Database sizes vary from 5gig to 30gig. We try to keep the databases under 30gig cause they will compress down to 4-5gig. Anything bigger than 4-5gig times out on the network here cause it takes too long to tranfer across the network. I know it's not right, but it's what I have to deal with here.

"to a different machine than to dump them to tape" - we dump them locally to disk, zip them, then tranfer them to a differnt machine.

 

[DrSql]

Depending on the size of DB and server performance.
Outline for backups:
1.) Daily full backup if the DB is below 100Gb. Schdule around 6.00 PM or 8.00 PM depedning the begining of your offpeak.
2.)Monitor the time to complete that backup.
3.)Schedule your Server backup with buffer of 1 hour expecting future growths. Or you can schedule a bacth file to execute.
4.)Then you can schdule a Trasnaction log to backup for a 2 or 4 hour window. Create another batch file to copy that file to another server after trans backup compltetd.
If you need any clarifications.


Dr.Sql
Good Luck.

 

[SQLBill]

I administer a 200+ GB database (operational 24/7) and I backup this way:

Full backup once a day beginning 6PM
Differential once a day at 6AM
Transaction Log backups eery 4 hours throughout the day.

All of those backups are to disk.

Two hours after a backup is done, the backup file is copied to tape.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

BCP dumps of the tables are basically worthless.

Each table will be out of sync with the other tables, not to mention there is no schema.

What would happen if you were to BCP all the data base in? All the tables would be out of sync with each other.

Full backups at least once a week, with differential backups daily, and transaction log backups every couple of hours based on how much data you can afford to loose.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

Donate to Katrina relief

 

[Catadmin]

When you talk about zip files, you're talking about compressing the backups, right? Not saving them to a zip disk?

Reason I ask is zip disks are notoriously fragile. They tend to only be good for a certain number of writes before falling apart or corrupting the data on them, the drives themselves will break if you look at them cross-eyed and they just plain and simply aren't built for long term data storage.

They may be easier to save to than a tape backup, but they are less reliable and your disaster recovery methods may suffer as a result if you depend on a zip disk to protect your data.

Anyway, that's my two pennies worth. Hope it helps you out.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Will192]

I am not talking about ZIP disks.