February 24, 2009 at 12:44 pm
Hi,
As stated by others media errors are of cause a concern, but other things are even more relevant in my view.
Restore procedures, just restoring the DB in many cases does not generate a working environment, one have orphaned users and specific login acounts and object level permissions linked to these. So a restore test is more than a readable backup, it's a working application system to the end user.
In our shop I have had a even worse issue, sys admins release stuff into prod with a create database in, without getting the new database added to a backup maintenance plan. So I have more than once found a DB not being backed up at all for more than a month.
Another hard lesson learned, is to take a copy of the *.mdf and *.ldf files of the system databses over into a recovery folder with the SQL serer stopped after final SP have been deployed at SQL Server install time. This gives us the posibility to get a server running, without manual re creating the system databses in case of a compltete server crash. Copying a few files is much faster than re creating all the system databases. And yes I have experienced even a complte RAID 5 with hot spare to fail due a faulty RAID controller.
Of cause the copy have to be red one at every sp deployment.
//SUN
February 24, 2009 at 12:50 pm
I think there is also another aspect to this issue. Security. When you back up your database and store the backups on tapes, disks, online or whatever ather method you may come up with you in fact increasing a risk of the data being stolen.
How many of you encrypt your backups?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 24, 2009 at 12:58 pm
JacekO (2/24/2009)
I think there is also another aspect to this issue. Security. When you back up your database and store the backups on tapes, disks, online or whatever ather method you may come up with you in fact increasing a risk of the data being stolen.How many of you encrypt your backups?
Great point, JacekO. Not enough people do.
Not that encryption will protect against the most common data thefts (inside jobs): http://is.gd/kD7X
February 24, 2009 at 1:00 pm
Hi,
Agree with the last poster, with the size of removeable storage devices these days a stolen backup is very easy to get out of a server.
Yes we do encrypt databases with client sensitive data. But how many scrambles their client sensitive data in dev environments? And how many external consultants on 3 month or shorter contracts have access to these data?
//SUN
February 24, 2009 at 1:24 pm
Another thought.
I do not believe in backups.
I believe in double or triple backups. And if possible using different methods and media types.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 24, 2009 at 3:01 pm
Soren Nielsen (2/24/2009)
...But how many scrambles their client sensitive data in dev environments? And how many external consultants on 3 month or shorter contracts have access to these data?
All of these are levels of managed (or unmanaged) risk. At the end of the day, the world still runs on trust. If one cannot be trusted, that one should not even be there.
Follow best practices, and forget the worries. If the worst happens, pick up the pieces and keep going. Worrying about it costs more legislation (SOX) and work hours than any such loss ever actually took away.
Most of the biggest data or "intellectual property" losses in history were caused openly by the companies or countries in control, not the hacker, nor the internal sneak.
Mr. Gates started from just such a situation... IBM was unable to recognize (until far too late) what a big deal the software industry would become - their focus was machines. Consider Mr. Jobs and PARC... Xerox did themselves little service in that undertaking... WYSIWYG was born there; their focus was on copiers and similar machines, so Mr. Jobs made Apple largely from that experience. Consider Mr. Einstein and the atomic bomb... Germany all but threw him out... not to mention Werner Von Braun who from the opposing side was studying American physicist Robert H. Goddard's research, to build German V2 rockets, in a time when America had little to no value nor understanding for Mr Goddard's work, and after the war America took him out of Germany and he became a huge American household name.
How many companies have been started by those who left another related enterprise?
The most important resource a company or country has is the people that make things move. The second most important resource is the ideas those people have. All the data in the world pales by comparison. No amount of backups will stave off bad management decisions, which are the greatest enemy to both security and data... like breaking client trust and selling off client personal data and polluting the pool of potential long term relationships, and by innundation of offerings, just to make a few immediate bucks. The data in databases is largely transactional and dead once recorded. It's loss rarely has the ability to drop a company out of business, unless that business is to protect another's data.
February 24, 2009 at 3:39 pm
I've had to restore a few things from tape over the years, and found that more than a third of the time the tapes ended up being a form of write only memory. One restore that didn't work after a tech reformatted the wrong hard drive required a tape from three days earlier, plus a full day down for five hundred phone people while data was reentered. I am only confident when I can restore to a different machine and insure it all works afterward, but even then still am worried about tapes that can't be read.
February 24, 2009 at 8:17 pm
Not only we restore *all* our backups we run checkdb and data_purity checks on all our "main" user databses as frequent as possible. It is all automated and when something fails we know immediately.
In additon live standby copies (logshipping) are also available for those "fat finger" moments 😉
* Noel
February 24, 2009 at 9:50 pm
You restore all user db backups every day? Automated to a remote server?
How much data is there?
February 25, 2009 at 5:03 am
Maybe I should do it more often but I restore into a shadow database once a month and access a selection of known changes, so far so good but better than nothing I suppose
Glen Parker 🙂
February 25, 2009 at 6:14 am
raymond.glaser (2/24/2009)
For us newbies - Would someone please give a hint as to how you verify a database backup ?
Hi Raymond
You see your maintenance plan?
See the Backup Database Task?
Go in there and tick the box marked "Verify Backup Integrity"
I agree that this should be the default
hth
Jack
February 25, 2009 at 7:17 am
Steve Jones - Editor (2/24/2009)
You restore all user db backups every day? Automated to a remote server?How much data is there?
It's not all that unusual it seems. There's a setup in our company, predating my arrival, where certain SQL 2000 boxes do their backups locally, and a remote archiving utility, in our case Netbackup, stores those backups to tape for long term.
However, on these special boxes, after a full backup is done and the Netbackup completes, a remote copy of the most recent backup takes place where it is copied to a staging area of a restore server. That server restores the backup(s) during the day and as it is isolated, no need to worry about it's impact on the business. This seems a relatively cheap way to add a layer of verification to your backups. You don't need something particularly high performance or high price, just something with a lot of hard drive space.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 25, 2009 at 9:03 am
Gaby,
I think this works to a point. When I worked at a small company, 3 or 4 instances, 5-10 databases, this is easy. When I worked at JD Edwards, we had hundreds of SQL Server instances. We backed up about 300GB of database stuff a day in 2000, which was 1/4 of our entire backup space. Most servers had 30-50GB of space, and the only practical way of doing this would be to restore the same server which impacts production.
Now I imagine that there are companies with a few TB of backups they'd need to restore, and it can be hard to justify. Sure a 1TB desktop drive is cheap, $4-500, but you might not get 1TB copied and completely restored in a timely manner. And what if it's bad? You might not be able to run another backup during the day.
I think knowing if you have an issue is important, but I'm not sold on the every backup needs to be restored. Especially as I've found in the critical systems I might not be able to do anything about it.
February 25, 2009 at 9:13 am
Steve Jones - Editor (2/25/2009)
Gaby,I think this works to a point. When I worked at a small company, 3 or 4 instances, 5-10 databases, this is easy. When I worked at JD Edwards, we had hundreds of SQL Server instances. We backed up about 300GB of database stuff a day in 2000, which was 1/4 of our entire backup space. Most servers had 30-50GB of space, and the only practical way of doing this would be to restore the same server which impacts production.
Now I imagine that there are companies with a few TB of backups they'd need to restore, and it can be hard to justify. Sure a 1TB desktop drive is cheap, $4-500, but you might not get 1TB copied and completely restored in a timely manner. And what if it's bad? You might not be able to run another backup during the day.
I think knowing if you have an issue is important, but I'm not sold on the every backup needs to be restored. Especially as I've found in the critical systems I might not be able to do anything about it.
Agreed, it would have to be a manageable number and if the company has the luxury of more than a few DBA's, assign a block of servers to each DBA. But ultimately, I'm still sold on the verify after backup checkbox, such as in the maint. plans, or going for a RESTORE VERIFYONLY in your script. heh heh, i guess it's never easy.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
February 25, 2009 at 9:19 am
Definitely not easy.
It's a great idea if you can make it happen. I've gotten away from "every one" since the SQL Server process, if it makes it to disk, has been rock solid for me. If there's a media failure there, it is as likely to happen when I need the backup as when I test it.
For tapes, however, you do need to pull at least one backup off regularly, perhaps monthly, to be sure media is working. At JDE, we ran 2 tapes every night (1 carried offsite) for this reason.
Viewing 15 posts - 31 through 45 (of 56 total)
You must be logged in to reply to this topic. Login to reply