Great! It works now. So can I delete the old trn file next week if I start running this job instead? It's just a log backup right?...

where would I add that code?

SQLKnowItAll (5/23/2012)

---

Do something like this:

DECLARE @diskPath VARCHAR(150)

SET @diskPath = 'C:\TransactionLog\Production_Log'+ CAST(YEAR(GETDATE()) AS CHAR(4)) + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + CAST(DAY(GETDATE()) AS VARCHAR(2)) + '.trn'

--PRINT @diskPath

BACKUP LOG production

TO DISK = @diskPath

WITH RETAINDAYS=1,

DESCRIPTION...

can I do this as a maintenance plan? or is something I will need to write in t-sql?

Is this best practice? Why can't I use a single log file and tell it to purge every couple of days?

so add the following?

CAST(hour(GETDATE()) AS CHAR(4)) + CAST(minute(GETDATE()) AS CHAR(4))

and i would have to manage multiple files? or are they going to delete themselves?

Now I'm getting this error

Executed as user: NT AUTHORITY\SYSTEM. The medium on device 'H:\TransactionLog\Production_Log.trn' expires on May 24 2012 10:00:01:000AM and cannot be overwritten. [SQLSTATE 42000] (Error 4030) BACKUP...

That makes sense. Is it not a default switch when doing a backup? I need this 200GB trn file to shrink before I do any further changes. ...

Ok I will find out tomorrow. Thank you so much for all of your help!

anthony.green (5/23/2012)

---

Your confusing the TRN and LDF, they are NOT the same thing

TRN = Transaction Log BACKUP

LDF = Transaction Log

How big is the LDF file for the Production database?...

if I set retainday=2 then my transaction log will commit changes to the database and shrink to default size? Will I still be able to restore point-in-time or just...