Backup database?transaction log?or both?

Hi all. I'm not good at the database, but recently I build a website and
need to maintain it. I read some books but still don't know which way is
best for me to backup the database. What I want is I can restore data just
before the database was damaged. But it's difficult to me to understand the
diffrences between database backup and transaction log backup. or do I need
to do them both? please help, many thanks.Yes. To have the ability to do a point in time recovery you will need to
set the database recovery mode to Full and configure a backup strategy. You
can use the SQL Maintenance wizard. It works ok for most small to medium
size databases. Basically a Full backup is exactly as it implies. It's
everything as of the end (all data and commited data changes) of the backup.
Each full backup will contain a last lsn. A number that identifies the last
log sequence in the backup file. Each transaction log will have a first and
last lsn as well. In order to recover you will need cover a range of lsn
from the first lsn in the full to the lsn that contains the time at which
you wish to recover to. There can be no gaps in the number sequence.
Performing a new full backup from time to time say once per night means that
your recovery is simplier because you get a new first lsn. The transaction
logs that were run pior to the new full are generally not needed unless you
need to go back in time before the most recent full. Technically you only
need one full backup if you are willing to keep every transaction log backup
and restore all of them to the point of damage.
Bottom line is to decide just how much data can be lost with a failure. Say
15 minutes. Configure SQL maintenance to do a full during non peak time
once per day and backup transaction logs every 15 minutes.
"mizi" <haha@.haha.com> wrote in message
news:Xns97185D5E7AA8hahahahacom@.207.46.248.16...
> Hi all. I'm not good at the database, but recently I build a website and
> need to maintain it. I read some books but still don't know which way is
> best for me to backup the database. What I want is I can restore data just
> before the database was damaged. But it's difficult to me to understand
> the
> diffrences between database backup and transaction log backup. or do I
> need
> to do them both? please help, many thanks.|||Yes, at least -minimum- you will need full database and transaction log
backups. Doing both full and transaction log backups will allow you to
recover your database to a specific point in time, for example, a few
seconds or minutes before your database failure.
How often to do the backups? Depends on the size of the database and how
long the backup would take. If possible, do nightly full backups. Also, take
a look at differential backups.
Once you decide your backup strategy, test and practice restoring your
database, maybe on another server. That is the only way to know if your
recovery strategy would work when you need it. Another good way to test is
to use your backups for your development environment.
Ben Nevarez
"mizi" <haha@.haha.com> wrote in message
news:Xns97185D5E7AA8hahahahacom@.207.46.248.16...
> Hi all. I'm not good at the database, but recently I build a website and
> need to maintain it. I read some books but still don't know which way is
> best for me to backup the database. What I want is I can restore data just
> before the database was damaged. But it's difficult to me to understand
> the
> diffrences between database backup and transaction log backup. or do I
> need
> to do them both? please help, many thanks.|||Hi,
In a database there are two files datafile and transaction file.
In datafile all the transaction are saved when committed and in
transaction file all the information is stored what u are doing with
the database.
if u want to backup the database normal backup SQL server backup the
datafile(.mdf) and Transaction log(.Ldf) and creates a backup file with
extension.BAK.
If u database is small for ex:2 or 3 GB then u can have ur database
backup 2 or 3 times a day.
if ur database is big enough that u cannot backup the database so
frequently then u have to take a full backup and then tlog backup.
For transaction log backup the recovery mode of the database should not
be in simple mode.It should be in Full or Bulk.
Transaction log backup is not allowed in simple recovery mode.
So if ur server crashed then u can recover it till the last tlog
backup.
just restore the last backup and then all the tlog backup file in
sequesnce u backed them up.
if u read books online then u will get more information related to this
topic.
HTH
from
Doller
Ben Nevarez wrote:
> Yes, at least -minimum- you will need full database and transaction log
> backups. Doing both full and transaction log backups will allow you to
> recover your database to a specific point in time, for example, a few
> seconds or minutes before your database failure.
> How often to do the backups? Depends on the size of the database and how
> long the backup would take. If possible, do nightly full backups. Also, take
> a look at differential backups.
> Once you decide your backup strategy, test and practice restoring your
> database, maybe on another server. That is the only way to know if your
> recovery strategy would work when you need it. Another good way to test is
> to use your backups for your development environment.
> Ben Nevarez
>
> "mizi" <haha@.haha.com> wrote in message
> news:Xns97185D5E7AA8hahahahacom@.207.46.248.16...
> > Hi all. I'm not good at the database, but recently I build a website and
> > need to maintain it. I read some books but still don't know which way is
> > best for me to backup the database. What I want is I can restore data just
> > before the database was damaged. But it's difficult to me to understand
> > the
> > diffrences between database backup and transaction log backup. or do I
> > need
> > to do them both? please help, many thanks.