T Log Backup Job - Troubleshoot

Question

T Log Backup Job - Troubleshoot

aby_dvd

Ten Centuries

Points: 1310
More actions
September 27, 2011 at 9:10 am

#258011

For one of my instance , management tools are not properly installed .Hence, management tools are not working propely .
The requirement is to create transactional backup job every 15 mins .
I created a job by scripting the database -> backup -> t-log....but it is generating only one SINGLE file for me ,
Rather , i want it to create DIFFERENT backup files everytime the job is running .
I am not able to do that .
Please help
Also , i wish to delete files older than 3 days .
Please suggest .
TIA \M/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

abhishek_dwivedi03 (9/27/2011)
For one of my instance , management tools are not properly installed .Hence, management tools are not working propely .
The requirement is to create transactional backup job every 15 mins .
I created a job by scripting the database -> backup -> t-log....but it is generating only one SINGLE file for me ,
Rather , i want it to create DIFFERENT backup files everytime the job is running .
I am not able to do that .
Please help
Also , i wish to delete files older than 3 days .
Please suggest .
TIA \M/

If you execute something like this, in an agent job, then I think it'll fulfil your requirements

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = 'BACKUP LOG [' + NAME + '] TO DISK = N''D:\BackUps\' + NAME + 'TransactionLogBackup' +

REPLACE(CONVERT(VARCHAR(12), GETDATE(), 102) + '_' + CONVERT(VARCHAR(8), GETDATE(), 108), ':', '.') +

'.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + NAME + '-Transaction Log Backup' +

CONVERT(VARCHAR(12), GETDATE(), 102) + CONVERT(VARCHAR(8), GETDATE(), 108) + ''',

SKIP, NOREWIND, NOUNLOAD, STATS = 10'

FROM sys.databases

WHERE recovery_model_desc = 'FULL'

AND NAME <> 'model'

EXEC(@SQL)

PRINT 'Transaction Log Backed-Up'

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

The easiest way to do is:

Use maintainance plans:

1. drag the cleanup task and set it to the path folder that contains the log files backups and set to delete older than 3 days

2. Drag the backup tasks and set it to log backup and make connection to the database and then schedule it to every 15 mins.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Jack Corbett SSC Guru Points: 184388 More actions · Answer 3

Check out the great maintenance scripts created by Ola Hallengren (http://ola.hallengren.com/), as they will allow you to schedule all this using SQL Server agent.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question