Full Database Backup

[Former Member]

Hi people,

I´ve the next doubt about the backups of MS SQL Server.

If I make a full database backup, the transaction log (SIDLOG1) are backed? Or I´ve to do the backup of transactional log separated?

In the note Note 44449 - Backup strategies with the SQL server, says this:

"If you run a complete database backup, the contents of the transaction log are saved (database dump), but the log is not shortened. The transaction log is only shortened when you save the log (transaction dump)."

[Former Member · Accepted solution]

A FULL SQL backup includes all of the changes that are contained in the LOG file, so that the backup is recoverable to any point in time from the beginning to the end of the time it takes to backup the database. However, it does NOT truncate the log file because you may still need a log backup in order to be recoverable to a point in time AFTER the FULL backup mentioned earlier, or if you have to utilize a different FULL backup in the event the other backup is corrupted, lost, etc.

For example:

Full backup A taken 11/8/2010, begin 8:00 AM, completed 9:00 AM.

Transaction Log backup 1 taken 11/8/2010, 11:00 AM

Transaction Log backup 2 taken 11/8/2010, 3:00 PM

Transaction Log backup 3 taken 11/8/2010, 7:00 PM

Full backup B taken 11/9/2010, begin 8:00 AM, completed 9:00 AM.

Transaction Log backup 4 taken 11/9/2010, 11:00 AM

Transaction Log backup 5 taken 11/9/2010, 3:00 PM

Transaction Log backup 6 taken 11/9/2010, 7:00 PM

If you need to recover to point in time 11/9/2010 at 8:30 AM, all you need is Full backup B.

If you need to recover to point in time 11/9/2010 at 5:00 PM, you need Full backup B, and Transaction Logs 4, 5, and 6.

If you need to recover to point in time 11/9/2010 at 5:00 PM, and Full backup B is corrupted, you need Full backup A, and Transaction Logs 1, 2, 3, 4, 5, and 6.

If Full backup B had truncated the log, then you would not be recoverable to any point in time after 7:00 PM on 11/8/2010.

[Former Member · Accepted solution]

More thanks Joey Stephens for your time,

How can to do the backup of transaction log on several bit files? with DB13 or Enterprise SQL Server.

[Former Member · Accepted solution]

Ruben,

When you say "several bit files" did you mean "data files?" .

If so, you can schdule transaction log backups wither using DB13 or Management studio.

DB13 method: DB13 --> select Transaction log backup, on action pad, and click on Add..

in Management studion, right click, database --> tasks -> backup and schdule.

regards,

Krishna

[Former Member · Accepted solution]

I refer to every time that a backup transaction log is made, the backup file of this archive how can split on several file.

[former_member524429 · Accepted solution]

Hi,

Try to execute the following "Backup Log" command in SQL command editor of SQL Enterprise Manager or you can create Task scheduler for the same.

Backup Log <SID>

to Disk = <device_name><with File1>,

Disk = <device_name><with File2>,

Disk = <device_name><with File3> With NO_TRUNCATE

GO

Try to specify multiple files in the [backup properties|http://i.technet.microsoft.com/Cc966495.ssbkre29%28en-us,TechNet.10%29.gif] of Transaction log backup (either for Tape/Disk backup).

Regards,

Bhavik G. Shroff

[Former Member]

Hello,

Transaction Log backup is usefull for Point in time Recovery. If you want to define backup stetegy, you should take a Full Database backup and differential database backup daily and at lease take backup the Transactiona log backup once in a Month/Quarter.

Thanks,

Rahul S.S.

[former_member524429]

Hi,

Transaction Log file(s) contains inactive portion (completed transactions) & active portion (open transactions) from first part of file to the oldest open transaction log. When you perform Transaction log backup, it will clear this inactive portion within this transaction log file and hence this will reduce the size of Transaction log file.

Refer SAP Note 421644 to get more detailed information.

Regards,

Bhavik G. Shroff

[Former Member]

I think Note 1297986 shoudl clear your doubt.