I would like to clear something up regarding Backup

[dvannoy]

I have posted a few messages here and am in the process of reading a couple of SQL Maintenance books etc.. But I would like to ask a couple of questions for the experts on backups.

I backup my DB's everyday at 2:00am(append)

Now, doing a full backup means backing up the Data as well as the Trans Log correct???

I used the filelistonly command to look inside the backup file and saw the data file as well as the log file..am I to assume both were backed up?(i would think thats the case)

so, if thats the case, if I ever needed to do a restore the data would only be good upto 2:00am that day correct? I would get the Trans Log and Data file up to that time.

what if I backed up the Log file a few times a day but only backed up the DB at 2:00am.. what would the outcome be if I needed to restore..

any advice or recommendations would be appreciated

Thanks in Advance

 

[nigelrivett]

You could restore any of the full backups followed by the sequence of transaction log backups following that backup to get to the point of the latest tr log backup.

One of the benefits of this backup strategy is that it means if you have a corrupt full backup you can go back to the previous one and restore all the tr log backups to bypass the corrupt one.

The full backup just backs up enough of the tr log to enable a restore.

I would advise backing up to separate files rather than appending as it is easier to manage.

see

http://www.nigelrivett.net/BackupAllDatabases.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ecobb]

Basically, if you do a database backup at 2:00am, and set your Transaction Log backups for every four hours (for example, starting at midnight: 12:00, 4:00, 8:00, etc...), if your database died at 1:00 am, you could restore from the previous 2:00 am full backup, and then restore each Transaction Log (in order) since the backup until the last one at 12:00.



Hope This Helps!

Ecobb
- I hate computers!