Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 24, 1999

Data Types - Page 5

By Christopher Shaw

BACKUP BACKUP BACKUP…

I don’t know any other way to say this.

It’s 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 it’s 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 it’s a bad tape drive? What if
it’s not my fault? Guess what? It is. If the tape drive is bad you should know it. If
it’s 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). It’s 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 Seagate’s 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
master database.

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 didn’t 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
time.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date