SQL Server Performance Forum – Threads Archive
Setting up transaction logs
Currently we don’t keep any transaction logs i.e all databases are set to ‘truncate log on checkpoint’ I want to enable this as part of our backup strategy but need to verify with you pro’s the process I am going to use. After a backup I will…. First I will put all databases in dbo only modeThen I will disable the bulk insert and truncate log on checkpoint options on all databases Next step is to create backup devices for the databases and schedule a differential for 8am, 12pm and 8pm Create another set of backup devices for my end of week full backup and schedule Then create backup devices for the transaction logs and set to backup every hour Next, restart SQL (not sure if I need to do this?) Finally take all databases out of dbo only mode and then sit back and admire my handy work Will this work and get my desired results? Does the differential backup purge the transaction logs or does that only happen with a full backup? I guess also that as I am only doing a full backup once a week that I need to ensure I have space for a whole weeks worth of transaction logs and for the differential backup that will grow in size throughout the week? Many thanks
As the transaction log is a serial record of all modifications that have occurred in the database as well as the transaction that performed each modification. Setting up transaction log backup job will certainly help you to recover the database in the event of failure. The backup cycle certainly looks well and no comments to say, if the database usage is high then keepup the time interval between Tlog backups to half-an-hour instead of one hour. Moreover to set the Tlog option the DB do not need to be in DBO use only. _________
Satya SKJ
]]>