May 23, 2012 at 10:39 am
Is this best practice? Why can't I use a single log file and tell it to purge every couple of days?
May 23, 2012 at 10:45 am
sql_hammy (5/23/2012)
Is this best practice? Why can't I use a single log file and tell it to purge every couple of days?
If you append to the same file every day, how would you delete a previous days information? Plus, if you have 10 t-log backups in one file and the file gets corrupted and is unusable, how many t-log backups do you lose?
Put each t-log backup into its one file, store it in several locations off server, and manage the number of files you keep. This can easily be automated so you don't have to do it manually.
May 23, 2012 at 10:51 am
can I do this as a maintenance plan? or is something I will need to write in t-sql?
May 25, 2012 at 6:32 am
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 = 'Log Backup for Production'
GO
But add in minutes or time to the file name. Then it is unique each time. You run this, take a full backup, and then delete your previous file. Then use this to backup your logs.
What's the syntax to add hour and minute to the file name?
May 25, 2012 at 6:36 am
just need to add in the nessesary date parts
select CONVERT(NVARCHAR,(DATEPART(HOUR,getdate())))
select CONVERT(NVARCHAR,(DATEPART(MINUTE,getdate())))
May 25, 2012 at 7:24 am
where would I add that code?
May 25, 2012 at 7:27 am
in the SET @diskpath statement
SET @diskPath = 'C:\TransactionLog\Production_Log'+ CAST(YEAR(GETDATE()) AS CHAR(4)) + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + CAST(DAY(GETDATE()) AS VARCHAR(2)) + put hours calculation here + put minutes calculation here + put seconds calculation here + '.trn'
May 25, 2012 at 8:49 am
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? Its not like i'm deleting the log itself right? I just need 7 days worth of PIT recovery.
May 25, 2012 at 8:55 am
yes its just the backups. SQL wont let you delete an LDF if its in use and attached to a database so you will know if your trying to delete the wrong file, plus from the 688MB you gave after running DBCC SQLPERF(logspace) you can pretty much be sure that a 200GB file isnt your LDF file.
Viewing 9 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply