SQL Server Performance Forum – Threads Archive
Transaction Log Backup Best Practices
Hi all- First time poster, long time lurker. Just trying to get some input on the best practices for backing up the transaction log. In our environment, we want to go for a full recovery model, with a 30 minute backup of transaction logs. Full database backup on a daily basis. DB is small, so it should be no problem. Full database backup – no problem. I create a SQL server backup device on disk, set a SQL Server agent job to backup and overwrite daily, and make sure that the .bak file gets picked up by the Veritas tape backup job. Transaction log backup – do I create a backup device for this? If so, I assume I need to ‘append’. If it’s on append, how do we ‘reset’ the backup device so that it doesn’t get too large? If you guys DON’T use a backup device for the transaction log, do you delete the old files manually or via script? Thanks for the input, and the great site! DongerLook at Tara’s backup scripts: http://weblogs.sqlteam.com/tarad They address your questions. I wouldn’t append. One file corrupted means you lose everything. Her script let’s you choose the days of retention, etc. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
You need to create two sql jobs..
JOB1 For taking full SQL Backup on daily basis. Step1 : take full backup of db with WITH INIT
—————————————————————————–
USE [Database_Name]
BACKUP DATABASE [Database_Name] TO [Database_Name_FullBackup] WITH INIT , NOUNLOAD , NAME = N’Database_Name Full Backup’, NOSKIP , STATS = 10, NOFORMAT
GO
— Database_Name_FullBackup is a backup device
step2 : take TL backup with WITH INIT
—————————————————————————–
USE [Database_Name]
GO
BACKUP LOG [Database_Name] TO [Database_Name – tran backup] WITH INIT , NOUNLOAD , NAME = ‘Database_Name transactionslog backup’, SKIP , STATS = 10, DESCRIPTION = ‘Database_Name transactionslog backup’, NOFORMAT
GO
— Database_Name – tran backup is a backup device JOB2 For taking tran log backup after every 30 minutes round the clock and append it in a single file. step 1 : take TL backup with NOINIT
—————————————————————————–
USE [Database_Name]
GO
BACKUP LOG [Database_Name] TO [Database_Name – tran backup] WITH NOINIT , NOUNLOAD , NAME = ‘Database_Name transactionslog backup’, SKIP , STATS = 10, DESCRIPTION = ‘Database_Name transactionslog backup’, NOFORMAT
GO — Read books online under backup topic for init and noinit details. — Further you can set Tape drive backup or xcopy of these files before full backup.. to archive old backup sets.. if required. RESTORATION FROM THIS BACKUP FILES IS ALSO EASY LIKE : –
Restore database [DATABASE_NAME]
FROM [Database_Name_FullBackup] WITH NORECOVERY
go
—————————————————————————– declare @count int
set @count= 1
while @count < 48
begin
RESTORE LOG [DATABASE_NAME] FROM [Database_Name – tran backup] WITH FILE = @count, NOUNLOAD , NORECOVERY
set @[email protected]+1
end
go — IF YOU HAVE TAKEN TAIL BACKUP OF TRANSACTION LOG (in case of disaster) APPLY IT HERE IN THE LAST
—————————————————————————– Restore database [DATABASE_NAME1] WITH Recovery
go Deepak Kumar –An eye for an eye and everyone shall be blind
Guys, thanks for the responses. Very very helpful. I appreciate it! Donger
]]>