BACKUP BACKUP BACKUP
I dont know any other way to say this.
Its never a matter of "If I am going to lose data", but when. Things
are going to crash. It will happen, and to some people like me its more often. The
first SQL class that I took the instructor was talking about the job responsibilities of a
DBA. When he spoke of backups, he said , "In most companies if there is a database
crash and there is no recoverable backup that DBA is fired". In my opinion this
should be the case. If nothing else I think that the lack of recoverable backups is cause
for termination. Many people will say what if its a bad tape drive? What if
its not my fault? Guess what? It is. If the tape drive is bad you should know it. If
its not recoverable it is your fault.
As far as I am concerned, and the company that I work for is concerned, number one
priority for me is keeping the database up. The company that I work makes their money by
their data. This is the way we pay the bills. If there is no data there is no job.
When your database crashes there are ways to minimize loss. The way to do this is the
transaction log. The server that I have is up 24/7. I backup the full database once a
night (then I run DBCC). Its a backup to disk in a directory that I call backup. In
the directory of backup, I have a folder called trans. In this folder I have created six
backup devices named trans1 to trans6. During normal business hours I backup the
transaction log to a trans device. The first transaction log back up is done to trans1,
the second is done to trans2 and so on. I am running Seagates backup program to run
a backup to tape of the transaction logs from the disk shortly after the backup is done. I
do one of the main databases after that disk backup is done. This schedule is kept for 21
days. Two of the tapes are then taken off site to ensure that if there was a natural
disaster that I still have a copy. Before a new 21-day cycle is started a full restore is
done on a separate machine to ensure good backups. The DAT is cleaned every day and we
keep a cleaner tape in the drive at all times. After this is done, do the same for your
This is where it can get scary. You have done all this prep to do your back ups, you
have the cycle done and you may have even been able to do a restore. Then you forget the
master database. Here we go again. I have done this and I am here to share so let the
laughing begin. I used to back up the master once a week. Sounds good right? I thought so.
The company that I use to work for wanted an old copy of a database installed. The copy
was close to six months old. The reason that they wanted this copy installed was there was
a change of data. There was a huge change in the data and they wanted to verify some
numbers. I was an Assistant DBA at this company and I was not doing the backups at all.
They were being handled by the DBA. I had that data and the database but they had no
master. I am sure now that there is an easier way to do this (DISK INIT, DISK REFIT) but
at the time I didnt know this.
I was there for days trying to restore the data. To sum it all up, backup the Master at
the same time as the database. In my case, the Master is very small and it is worth the