Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ws5926
Yak Posting Veteran
88 Posts |
 Posted - 2005-09-13 : 12:39:45
|
| 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? Live to ThrowThrow to Live |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-13 : 12:52:02
|
| "BCP dump of all data tables"Seems a bit extreme - but we used to do that back in 6.5I would add a transaction backup hourly, or ten-minutely, straight away. It would save you from failures other than total disk loss."Oops I've just dropped a table by mistake, can you re-key the last 23.59 hours work please" - that type of embarrassment!"to a different machine than to dump them to tape"I'm a bit surprised by that - you need to make sure that you have a decent enough history of files to recover from - depends on your data of course, but I reckon on being able to restore to a moment in time for the last 7 day, to a day for the preceding, say, 14 days, and to a weekly backup for the preceding couple of months. Generally a monthly backup tape is kept "forever", so in the event of, for example, undetected fraud it would be possible to restore snapshots way back in time to get a picture of what took place.Kristen |
 |
|
|
ws5926
Yak Posting Veteran
88 Posts |
Posted - 2005-09-13 : 13:16:09
|
| 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.Live to ThrowThrow to Live |
|
|
|
madness
Starting Member
13 Posts |
Posted - 2005-09-13 : 17:18:31
|
| "Anything bigger than 4-5gig times out on the network here cause it takes too long to tranfer across the network."I'd suggest striping the backup across multiple files and then compressing those individually. Say 1 stripe per 2gb of data, up to a maximum of 64 stripes. Then you can send one file at a time across the network. Easier to recover and will minimise your timeouts. |
|
|
|
ws5926
Yak Posting Veteran
88 Posts |
Posted - 2005-09-14 : 09:04:37
|
| BCP dump and scripts of databases are useful if you have a table that someone dropped or wiped out on accident. We have actually had to restore more often from BCP dump than from a full dump.The big question that I had hoped to get answers on is how does everyone backup the root drive? I know it's just a reinstall of the operating system, but then you have to go in and do all of the little tweaks that everyone does.Thanks for all of the replies.I will look into the striping of the backup. What versions of SQL Server support striping?Live to ThrowThrow to Live |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-14 : 14:06:03
|
"a table that someone dropped or wiped out on accident"I prefer to do that by restoring the latest backup to a TEMP database, and then copying the table over - that way I can use the latest TLog backups to get up-to-the-moment of the "accident".But then we have a public execution of the culprit, so it doesn't happen very often  Kristen |
|
|
|
|
|
|
|
|
List of Backup Procedures