| Author |
Topic |
|
Bobba Buoy
Starting Member
36 Posts |
 Posted - 2003-08-03 : 08:24:01
|
| When you do a full back-up of a sql server db, does that include the transaction logs or do they still have to be backed up seperately?Thanks! |
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2003-08-03 : 18:17:26
|
| The full backup includes transaction log. But you still have to backup transaction logs between full backups, in order to be able to recover datavase from a disaster.regards,Sérgio MonteiroTrust in no Oracle |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-04 : 12:32:29
|
quote:
Originally posted by shsmonteiro
The full backup includes transaction log. But you still have to backup transaction logs between full backups, in order to be able to recover datavase from a disaster.regards,Sérgio MonteiroTrust in no Oracle
The full backup does NOT include the transaction log. You have to explicityly run BACKUP LOG to backup the transaction log.Tara |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2003-08-04 : 18:31:02
|
quote:
quote:--------------------------------------------------------------------------------Originally posted by shsmonteiroThe full backup includes transaction log. But you still have to backup transaction logs between full backups, in order to be able to recover datavase from a disaster.regards,Sérgio MonteiroTrust in no Oracle--------------------------------------------------------------------------------The full backup does NOT include the transaction log. You have to explicityly run BACKUP LOG to backup the transaction log.
Tara, Full backup DO include transaction log. From BOL:quote:
DATABASESpecifies a complete database backup. If a list of files and filegroups is specified, only those files and filegroups are backed up.Note During a full database or differential backup, Microsoft® SQL Server™ backs up enough of the transaction log to produce a consistent database for when the database is restored. Only a full database backup can be performed on the master database.
And more from BOL:quote:
Full database backup, which backs up the entire database including the transaction log.
the two sentences above came from "BACKUP DATABASE" Topicregards,Sérgio MonteiroTrust in no Oracle |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-04 : 18:42:24
|
| You have to understand what that means though. It only backups up enough of it to restore a consistent datbase. If you do not backup the transaction log explicitly, you can NOT restore to a point in time. Backing up the database just grabs a snapshot of the database and does not allow restoration to a point in time.Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-08-04 : 19:39:25
|
| You are both right. A Full backup does backup the log, but it isn't the same as a log backup, So no Point in time recovery. Also, it doesn't truncate the log like a log backup would. So if you have a full recovery model, and you only do full backups, your log will grow out of control, and you will likely be back here at SQL Team asking "How do I shrink my 30GB log on a 200 MB DB" (Quite a popular question).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-04 : 19:44:11
|
| Thanks Chad! I was trying to find that in BOL and it eluded me, I know I'd seen that before. |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2003-08-04 : 19:59:12
|
Tara,that's because I wrote:quote:
But you still have to backup transaction logs between full backups, in order to be able to recover datavase from a disaster.
I mean for this sentence that Full backup is not enough for full recovery of a crash. But the question was: "Does the Full backup include transaction logs." The answer is YES. But, is this the right recovery strategy? The answer is NO.Sérgio MonteiroTrust in no Oracle |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-04 : 20:01:34
|
| But you can recover the database in case of a disaster without the transaction log backups, you will just lose some data. It is very important that whoever is responsible for backing up a database is aware that they need to backup the transaction log with BACKUP LOG if they require the ability to restore to a point in time in case of a disaster. It should be mentioned that the database must be set to FULL recovery model in order to have this ability.Tara |
|
|
|
Bobba Buoy
Starting Member
36 Posts |
Posted - 2003-08-05 : 15:42:32
|
| This has been incredibly helpful! Thanks to all who contributed!! So I should do Transaction Log backups if I want to recover completely from a disaster. I am relatively new at all of this and work most of the time out of wizards. Is there a Backup Log wizard? I don't recall seeing that as an option when I have run my backups before.Also, how do I tell what recovery mode my database is in?Thanks!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-05 : 15:45:04
|
| You can setup the backing up of the transaction logs using the maintenance plan wizard. This is probably the same wizard that you used to setup the database backups. I would suggest backing them up about every hour or so. We actually perform them every 15 minutes and then ship them to another location for disaster recovery purposes. You also need to check if your recovery model is set to FULL. Go to the database in EM and right click on it. Go to properties, then to options. Recovery model must be set to FULL in order to be able to backup the transaction logs.Tara |
|
|
|
Bobba Buoy
Starting Member
36 Posts |
Posted - 2003-08-06 : 08:12:50
|
| Thanks. I checked and it is set to full recovery. But I am really frustrated!!! WHY WON'T MY SCHEDULED JOBS RUN!!!!!!!!! AAAAGGGGHHHH!!! |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2003-08-06 : 09:20:50
|
| jobs not running have almost always been permissions issues.Check ownership of job and objects they run against, as well aslogin they run under.Voted best SQL forum nickname...."Tutorial-D" |
|
|
|
|
BackUp Question