Re: SQL 2005 Shrink issue

Tech-Archive recommends: Speed Up your PC by fixing your registry

Are you able to clarify the tranasctional log backup, log backup and any
other types of backup we're able to do in SQL 2005? I think I may be getting
confused over some of these.

You can backup the database, which is typically just called a backup.
Backing up the database gives you a file that you can restore somewhere for
testing or for recovery purposes. It is basically a point in time snapshot
of your data.

You can also backup the transaction log in between full backups. This
allows you to take that full backup snapshot, restore it, and then apply the
log backups to recover up until a point in time. Without log backups, if
you take a full backup every night, and then you lose your disk at 4 PM, you
lose all of the changes to your database that happened after the backup. If
you have transaction log backups every 15 minutes, you can have little or
even no data loss.

(Assuming, of course, that you put the .BAK files on a different disk than
your data files reside on. If you put them in the same place, you will lose
both.)

Books Online can explain this in much more detail than I can possibly do in
a newsgroup post.

We run backups nightly, creating a new .bak each time and we can go back up
to 2 months.

In FULL recovery mode, this is not a wise backup model. FULL recovery mode
is meant to allow you to recover fully to a specific point in time. However
to do so you need to back up your logs periodically in order to apply them
to get up to a point in time. If you don't back up your log multiple times
throughout the day, and only do a full backup every night, then the log will
get bigger and bigger... it is waiting for you to back it up so it can free
up space. If you don't back it up to free up the space used by what should
be inactive transactions, the log needs to keep those as active because you
haven't backed it up. In your current mode, you have no more safety than in
simple recovery mode. Whether you need to be in full recovery mode, or can
switch to simple recovery mode, depends on your tolerance for data loss. In
a catastrophe, if it is okay to lose all data that has changed since last
night's backup, then switch to simple recovery mode (this will prevent the
log from growing, because in simple recovery mode, it doesn't need to hold
all of your active transactions). Otherwise, I would create a job that
backs up the transaction log periodically throughout the day.

A

.

Relevant Pages

  • Re: Large Transaction Log Backup after Database Backup
    ... script and see for yourself that the database backup doesn't truncate the ... ALTER DATABASE steve SET RECOVERY FULL ... But my understanding was that the transaction log was> truncated after a full database backup. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... Backup database does not empty the transaction log files. ... How to Shrink the SQL Server 7.0 Transaction Log ...
    (microsoft.public.sqlserver.server)
  • Disaster Averted?
    ... Some process, unknown at this point, flooded a transaction log to the point ... the .mdf file was 0 bytes according to EM. ... Following the backup, I ... the database was now Suspect! ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction Log Size
    ... We currently use a 3rd party backup agent to backup our SQL databases and ... transaction logs (Commvault Galaxy iDataAgent for SQL) and the database is ... Because we are using a 3rd party agent to backup the database, ... > here are some articles on how to shrink your transaction log. ...
    (microsoft.public.sqlserver.server)
  • Re: Transaction log problems
    ... IF you have to shrink a database or file it ... sure that you get that database backup on tape or to another machine. ... > We are using SQL Server 2000 on Windows Server 2003 Standard Edition, ... > One of the databases has recently begun showing large growth of the> transaction log and it is causing us some problems. ...
    (microsoft.public.sqlserver.server)