October 8, 2003 at 7:36 am
I'm in my first DBA job (more of a babysitter than an admin at this point), and came into an environment that was, shall we say, less than organized. I'm trying to come up with a consistent backup policy to use as a starting point for all our MSSQL databases (currently around 50 DB's spread over 25 instances on 19 servers). I've found lots of good stuff on backup strategies, but next to nothing on retention. So, my main question to everyone is, do you have any advice, guidelines, etc., as to how long you retain backups of Tlogs and datafiles? In general, how far back do you usually want to be able to do point-in-time or full restores? I know a lot of this will depend on the DB, but I'm just looking for ideas to use as a starting point for our basic policy.
Thanks, Jeff
October 8, 2003 at 7:47 am
We keep three days worth of both on the actual server and a month's worth on tapes. Also, when thinking of retention, think of failover as well, we copy backups and logs to a secondary server in case of a critical server issue. We use a simple tool called Robocopy but you could look into log shipping or replication as well.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
October 8, 2003 at 10:44 am
How critical is your data?
Have you talked to the users of the data?
I'm REQUIRED BY LAW to keep my data for two years. I backup daily and when the data gets to a set size (six months) I do a copy of the .mdf and .ldf files to new tapes, then archive that offsite for two years. Then I delete the initial month of data and keep going. SOOOO,
Jan Feb Mar Apr May Jun
Regular backups being done
Now July 1st, I copy the .mdf and .ldf files. Delete the old January data and keep going with regular backups. Beginning of each '7th' month, I delete the '1st' month after doing a regular backup. (Since I already have Jan through Jun on an archive tape, I don't need to re-archive them). Once I delete Jun, I now have Jul, Aug, Sep, Oct, Nov, and Dec which I make a copy of the .mdf and .ldf files for archiving and keep on going.
-SQLBill
October 9, 2003 at 4:54 am
I've 30+ servers.
I keep 8 days on site on server. Each DB is backed up at least once a day, upto every 15 minutes.
I also have an older server that has a copy of every backup form every server.
I also (manually) log ship my mission critical DBs to 1. give a warm spare 2 to verify these backups. On tape, offsite, by law, we go back years
October 9, 2003 at 7:57 am
We keep the log backups for two days. Nobody will ask for a point in time backup for yesterday, but I like to have an alternative if the last full backup failed.
We keep daily backups for the last month and montly backups for 2 years. And the one time we needed the data that was deleted a year before, nobody could find the backup. We improved the tape storage strategy since then (I hope) 🙂
October 10, 2003 at 12:10 pm
Thanks for the input. We're looking at doing something like backing up all Tlogs and data to local disks on the servers, then copying over all full backups to our SAN (which will then get backed up to tape). Then all Tlog and full backups will be retained for two or three days (on the local disks). The thinking being we have backups on local disks for quick restores, and additional redundancy with the SAN storage and finally tape. We need to look harder at having standby servers for our critical DB's. The tricky part will be justifying the up-front costs to the bean-counters. Again, this would just be a baseline policy, modified as needed based on some determination of how critical the data is.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply