Backups' best practices
Posted on 2005-03-23
I've been a SQL Server DBA for many years now. I have never lost any data, so I must be doing something right :) And lucky too :)
I'm in a new position, and of course, there are hearts and minds to win over.
We're using 2000.
The databases I formerly managed were all high-volume OLTP databases...lots of transactions...lots of activity.
The backbone of my plan was to do Full Backups at a proper interval, supplement those with Differentials, and do tranlog backups at frequent intervals.
I backed these up to disk (not the same disk as the SQL Server), then when the network guys backed up their servers at night, of course the .bak files would get backed up as well.
The system worked well, the users were happy because I could do a Restore easily using either EM or Query Analyzer (users' managers sometimes wanted to go back to a specific point in time, just before someone had made an error :)
I found that disregarding SQL Server's excellent backup tools (IMHO) and relying on tape only to be unnecessarily complicating things, plus I gave up a lot of flexibility. Plus those tranlog backups are lifesavers in an OLTP system.
What do y'all think? I would welcome comments.
Pardon the Houston accent :)