September 13, 2005 at 10:59 am
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?
September 13, 2005 at 11:34 am
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.
September 13, 2005 at 1:07 pm
Our setup isn't anything like yours, so I don't know if you can glean anything useful from it.
We do twice weekly full database backups, daily differential backups, and hourly log backups to backup devices on local disk.
The backup files(devices) are copied to tape every night. By the end of the week, the files have an entire week's worth of backups for each database.
One copy of the weekly backup tape is retained in our data center. A second copy is stored offsite for one year.
Our goal is to be able to recover from disk to any point in time within the last week. We can recover from local tape to any point in time within the last 5-6 weeks.
Greg
Greg
September 13, 2005 at 2:02 pm
I don't understand the need to BCP the tables out to files and then script all the databases and jobs.
Doing a database backup includes the data as well as structures and code (views, triggers, procedures) and backing up the msdb database gets the jobs so it seems quite redundant and not very efficient to do these extra steps.
One thing that is missing from your description is transaction log backups. If you don't care about recovery to a point in time, you are fine, but if this is the case, make sure your databases are all set to use the Simple recovery model otherwise your log files will just grow.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 13, 2005 at 2:18 pm
Brian Knight also wrote a very good paper on Backup and Disaster Recovery. http://www.lumigent.com/go/ud22
Michelle
September 14, 2005 at 2:08 am
It does seem like an over complicated solution, but I guess I can't talk since we have clustered servers which log ship to an Offsite Warm Boxes (5 to 10 minute basis), then Daily local backups (For ease of restoring Test & Training DBs), Daily Offsite backups over the WAN (100mbit), on top of that we keep 4 Weekly Backups and 4 Monthly Backups offsite. It probably seems a little over cautious but better safe than sorry.
Any thoughts/suggestions?
September 14, 2005 at 4:10 am
Two points:
1. We have recently started to use a 3rd party backup product which compresses the backup and so removes the need for zip and unzip. This makes the backup process much faster.
2. Test your backups regularly by restoring onto another server and comparing the data. We restore every backup taken onto a standby server.
September 14, 2005 at 6:41 am
I have set up this plan for several clients. It's easy, simple, and works for 1 to multiple servers:
I agree with dcpeterson that the BCP dumps are redundent. But, make sure your always verify the backup as soon as it is done. SQL Server does seems to have a special version of Murphy's Law that any unverified backup will be corrupt at restore time.
Hope this helps
Mark
September 14, 2005 at 7:19 am
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.
September 14, 2005 at 8:55 am
If you've got people dropping or wiping out data from production tables that frequently, perhaps you should look at tightening down security to prevent that? I mean, are the people who are dropping tables DBA's who are just making mistakes or developers playing on a production server and not realizing the impacts of their actions?
In either case, if you have a standby server and restore your full backups there you can always export a table from the standby server and then recreate it on the production sever should the need arise.
-- J.T.
"I may not always know what I'm talking about, and you may not either."
September 14, 2005 at 9:04 am
I our 6 years with 2 large production servers, we have had to restore from SQL server backups twice. We have probably had to restore a certain table that was accidentaly wiped out probably about 8-9 times. It doens't happen a lot, but it does happen. I think one of those times was me and I am the main administrator of all the servers in our group. If it was happening any more frequently, then yes, I would tighten down security.
A couple years ago we had a developer in our group that wiped out a table and didn't realize that he was on production. I tightened down his access and asked my boss to have a talk with him. That same developer didn't see any danger in pulling up the table's rows in Enterprise Manager and scrolling through the live data to find the rows he was looking for. I couldn't make him understand that one mishit of a key could wipe out a couple rows of data and then it would be lost as he hit page down again. Anyway, just ranting.
September 14, 2005 at 9:31 am
Mmm I like the idea of BCPing the tables off the server on a nightly basis, that would save quite a bit of time on the odd restore due to user error, we normally just restore over the Test/Training DB and pull the details from there. Keeps the Test/Training Dbs up to date but can take a while to get the "urgent" bit of data a user has accidently removed.
Cheers for the idea
September 14, 2005 at 9:38 am
An even better solution to this is Lumigent's Log Explorer. I understand that LogPI does the same thing but I have used Log Explorer to recover from this kind of situation with great success.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 14, 2005 at 9:50 am
Nice, will have to have a look into that
September 14, 2005 at 12:27 pm
We have six servers in a poor man's high-availability setup:
(2) SQL servers
(2) web/portal servers
(2) file servers
One SQL server is primary and serves up database queries for the active web server. Daily backups are sent to the file server, and then restored to the secondary. Should the primary fail, we rename the secondary with the name of the primary. Tah da - instant recovery!
We do something similar with the web servers. The file servers robocopy from primary to secondary every night. Man, wouldn't a SAN be nice? If you have the bucks, I'd recommend an iSCSI SAN, makes a lot of this a lot easier.
For system drive backup you could use NT system backup/restore (requires a tape drive, i think), or you could use Ghost or something similar which just takes a snapshot of your entire drive and restore to that point from a bootable CD.
- Rick
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply