January 22, 2010 at 9:42 am
I work in a very large enterprise with many SQL databases (mostly 2005). I got put in charge of backing them up, and currently there is really no strategy at all. SCARY! We have a huge tape library gizmo that I can use, using ARCserve.
I would like to do backups using the built-in SQL tools as much as possible.
I think I should do something like this (and adjust as needed for various databases)...
1. Do a FULL backup Sunday night.
2. Do a DIFFERENTIAL the other 6 days at about 1:00 in the morning.
3. Do LOG backups every 4 hours, or 1 hour, or whatever is appropriate for the application.
I have pored through this board, but I don't see people asking questions that just baffle-the-heck out of me!
So these SQL backups create files on disk. The fastest place to store them is on a local disk, not over the network. But of course if the server blows up, then the backup as well as the original data files are lost = catastrophe.
So I assume I should pull these backup files to tape (which then get taken off-site) as quickly as possible, minimizing data-loss exposure. But I am talking on the order of 500 SQL Databases here -- tape is going to be running day and night!
I will have to set up some sort of round-robin system, presumably, in order to have a backup performed on the server, then have that backup pulled to tape soon thereafter. Does that sound right?
So I really should schedule SQL backups to occur sometime shortly before the tape sweeps by and pulls those files to tape. Again... I guess! So my schedule won't be as neat as I described above, I will have to do something much more complex. Like schedule backups 24x7, less-used databases have backups occur right during the day, then ARCserve sweeps in and grabs those files (it wouldn't make sense to have them only backed up on disk, and NOT pulled to tape). Sounds like a timing nightmare! Am I really the only one? Or am I overthinking this or something??
So when you "do a SQL backup", does this often mean you actually do TWO backups -- 1. a backup within SQL Server that creates backup file(s), then 2. a Tape backup that pulls those files to tape (which are then stored somewhere safe)???
And then what? Do I delete the backup files just after they've been pulled to tape? Or do you normally leave a full-restore's worth of files (the latest full + diff + logs) on disk so you could restore from that disk, and not have to go find tapes in your off-site archive?? So do you, for example, Sunday night do a FULL backup, then just after that delete all the old backup files??
And then Microsoft warns over and over that you really don't have any backups unless you TEST YOUR BACKUPS! Wow, now that's a project! Does everyone actually test their backups? (ya, right!) Do you document up an entire Backup and Restore Strategy? (ya, double-right!!)
I have this insane notion that I want to "do this right". I also have this nightmare that some night a 3 in the morning I'm going to get a call that a critical database has blown up or a hacker scrambled all the data, and I am the one who has to restore it to the most recent good point, and my boss and the CIO and the president and everybody is staring over my shoulder -- and I can either A) say, "oopsies, looks like I really wansn't doing backups, or the tapes we used were bad I guess we shoulda tested 'em, double-oopsies! 😛 or B) I can say... Oh, here on page 57 of the DBA Manual, in the Backup and Restore Section, you can see we perform monthly full restores of this database, so we know the tapes are working, and the restore process is X, then Y, then Z, and it is so nicely documented a monkey can do it (OK, I won't say that part, but having tested this process so many times I will know its true, for me, even at 3am), and lookie here I just rescued your whole company. Now give me a raise. 😎
Thanks for your insights!!
January 22, 2010 at 10:09 am
subs99 (1/22/2010)
...So when you "do a SQL backup", does this often mean you actually do TWO backups -- 1. a backup within SQL Server that creates backup file(s), then 2. a Tape backup that pulls those files to tape (which are then stored somewhere safe)???
SQL backup to local disk, a job to move the files to a central server, then backup from that server to tape. This provides a multi-tiered approach. If necessary, files from one of the locations may be deleted.
And then what? Do I delete the backup files just after they've been pulled to tape? Or do you normally leave a full-restore's worth of files (the latest full + diff + logs) on disk so you could restore from that disk, and not have to go find tapes in your off-site archive?? So do you, for example, Sunday night do a FULL backup, then just after that delete all the old backup files??
If necessary, files from one of the locations may be deleted. However, having a certain number of backups available on disk helps to provide for quicker recovery in the event of a failure.
And then Microsoft warns over and over that you really don't have any backups unless you TEST YOUR BACKUPS! Wow, now that's a project! Does everyone actually test their backups? (ya, right!) Do you document up an entire Backup and Restore Strategy? (ya, double-right!!)
It is true that you do not have a backup until you have tested a restore. Restoring every backup is not a common practice. However, having a process to test the backup and restore process is necessary. Part of that process dictates how frequently you will test backup and restore.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 22, 2010 at 2:37 pm
Another thing to consider is what is meant by local storage. Local storage can be direct attached - in that box or a SAN. The SAN can be in the same rack - or across the data center.
With that many databases, I would be pushing for storage on an Enterprise level SAN for the database files (mdf/ldf) and backup files (separate SAN if possible).
That gives you another level of separation where you can lose the server and/or rack - but still recover to point in time because the SAN is still okay.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 22, 2010 at 3:15 pm
subs99 (1/22/2010)
And then Microsoft warns over and over that you really don't have any backups unless you TEST YOUR BACKUPS! Wow, now that's a project! Does everyone actually test their backups? (ya, right!) Do you document up an entire Backup and Restore Strategy? (ya, double-right!!)
As a best practice, a DBCC check should be performed before every Full Backup. This makes sure that you have a Backup that is not corrupt...:w00t:
Testing backups on a periodic basis is a good practice and if your application is Mission Critical then a DBA can do himself a favor and TEST restore frequently.
Any good company Documents entire Backup and Restore Strategy...:-)
Personally I have been involved in two Test DR's that were part of our BCP.
Hope this helps..
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply