Help in creating Transactional log backups

Question

Help in creating Transactional log backups

pinky

SSCommitted

Points: 1997
More actions
January 3, 2007 at 10:57 am

#115851

Hi !!
I need help creating the transactional log backup's. Here is the scenario. Currently we are taking a full backup every night and differential backup every four hours. Now we would like to change this process. We would want to take the full backup every night and then the transactional log backups every 30 min. I have no idea as to how to set this up.
The database is now in simple recovery mode. Here are the steps i am thinking that i need to perform to create the job for the transactional log backups. correct me if i am wrong.
1. Create a job with the BACKUP LOG statement. (Can anyone please provide me the SQL for creating the BACKUP LOG;
BACKUP LOG [TestDatabase] TO [TransactionLog] WITH NOUNLOAD , NAME
= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =
N'BackUp the transaction Log every 30 minutes every day.', NOFORMAT
Please let me know if the statement above is correct or if i need to modify it. And also i dont want to append or overwrite the logs each time the job runs. How do i work on creating the new log backups each time the job runs)
2. Then after creating the job change the recovery mode to Full.
3. Take the full backup so that the Full recovery would be in effect.
4. Then start the transactional log backups.
Please let me know your thoughts. Thanks for the help

Viewing 15 posts - 1 through 15 (of 19 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You have the right idea. Look up BACKUP LOG in the Books OnLine. That's where you will see all the syntax options and examples.

-SQLBill

pinky SSCommitted Points: 1997 More actions · Answer 2

Thanks for your reply. I saw the syntax of the backup log in books online but i have a question here; i dont want to append or overwrite the logs each time the job runs. How do i work on creating the new log backups each time the job runs. Can you please let me know.

MohammedU SSCoach Points: 19644 More actions · Answer 3

You can use the following code...

Declare @Vch_FileName Varchar(255),

@Dt_DateAndTime Varchar(20),

@Vch_SqlString Varchar(255) ,

@Sys_Database Sysname, @BackupPath Sysname

select @Sys_Database = 'pubs', @BackupPath = '\\PC-MOHAMMEDU\c$\'

if right(@BackupPath,1) <> '\'

select @BackupPath = @BackupPath+'\'

select @Dt_DateAndTime = convert(char(8), getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')

-- convert(char(8), getdate(), 112)+'_'+ Replace(convert(char(8), getdate(), 14),':','_')

SELECT @Vch_FileName = @BackupPath+replace (@@servername, '\', '$')+'_'+@Sys_Database+'_tlog_' + @Dt_DateAndTime +'.TRN'

SELECT @Vch_FileName

backup log @Sys_Database to disk = @Vch_FileName

with init

MohammedU
Microsoft SQL Server MVP

pinky SSCommitted Points: 1997 More actions · Answer 4

Thanks Mohammed for your reply.

Can you please let me know if i need to include this code in the backup job i create? should the backup log statement be some what like this

BACKUP LOG [@Sys_Database] TO disk = @Vch_FileName WITH INIT, NOUNLOAD , NAME

= N'TransactionLogBackUp', NOSKIP , STATS = 10, DESCRIPTION =

N'BackUp the transaction Log every 30 minutes every day.', NOFORMAT

Please let me know. Thanks

MohammedU SSCoach Points: 19644 More actions · Answer 5

You don't need to...

If you want to backup the database name 'xyz'

REPLACE 'pubs' with 'xyz' in my code and create the job to run every 30 minutes...

OR

You can create a maintenace plan wizard to run tlog backup every 30 minutes...

Check BOL for "wizards, Database Maintenance Plan" topic...

MohammedU
Microsoft SQL Server MVP

pinky SSCommitted Points: 1997 More actions · Answer 6

ok i need to create a job and paste your code and change the database name!!! am i right!!!

sorry to bother you but one more question, how do i delete the transactional logs of the previous day? do i need to do it manually. Please let me know.

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 7

When I do my log backups I take a full and backup the transaction log with init, to initialize the tlog device. I then append all tlog backups to that device. I'll copy the tlog backups to another directory before I run the init on the device the next time my full backup runs. Then I'm not managing multiple files to check to see when/if I can delete them. You can write an os batch job that can check for creation time of the file and delete anything older than X.

Tom

pinky SSCommitted Points: 1997 More actions · Answer 8

Thanks for your reply. I dont think i understood your explanation clearly. Can you please explain in detail and also can you please let me know as to how to write an os batch job.

Thanks for your help

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 9

I'll put a timeline together for you. Maybe that will simplify things.

5pm-Start of Full backup job.

--Copy existing transaction log file to backup directory.(overwrites previous days tlog backup file.

--Do Full db backup

--Backup transaction log with init.

every half hour until 5pm.

--Back tansaction log without init.(to same file)

Hope that helps a little. I don't do much scripting so you'll have to go to a windows forum to learn how to write a dos batch file.

Tom

pinky SSCommitted Points: 1997 More actions · Answer 10

Thanks Tom for your reply. I am guessing that you are appending the transaction log backup each time the job runs. sorry if i understood wrong.

I would want to generate a new transaction log backup when ever the job runs. so that i would have multiple log backups instead of appending everything into one. (This can be accomplished by the code provided by Mohammed)

What i need to know is once the nightly full backup runs successfully i would want to delete all the previous days log backups or move those log backups into some history folder. Does it make sense? Can anyone please render any suggestions on this.

Thanks for any help.

MohammedU SSCoach Points: 19644 More actions · Answer 11

I know you are going ask this question so that I said you can use Maintenance plan which has the buildin functionality to delete older files.

If you are using my script you have to use xp_cmdshell command...you use the following script if you don't want go with Maintenance plans...

create the following procedure in your master database...By default it will 2 days old files see the usage of the inside the procedure...

CREATE Procedure Sp_BackUpTransactionLog @Sys_Database Sysname, @BackupPath Sysname ,@FileDelDays int = 2

As

Begin

-- Sp_BackUpTransactionLog @Sys_Database = 'pubs' , @BackupPath = '\\servername\e$\MSSQL7\Backup\Data\TransLogs\'

SET NOCOUNT ON

Declare @Vch_FileName Varchar(255),

@Dt_DateAndTime Varchar(20),

@Vch_SqlString Varchar(255)

if right(@BackupPath,1) <> '\'

select @BackupPath = @BackupPath+'\'

select @Dt_DateAndTime = convert(char(8), getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')

-- convert(char(8), getdate(), 112)+'_'+ Replace(convert(char(8), getdate(), 14),':','_')

SELECT @Vch_FileName = @BackupPath+replace (@@servername, '\', '$')+'_'+@Sys_Database+'_tlog_' + @Dt_DateAndTime +'.TRN'

-- SELECT @Vch_FileName

SELECT @Vch_SqlString = 'BACKUP LOG '+ @Sys_Database + ' TO DISK = '''+ @Vch_FileName +''''

SELECT @Vch_SqlString = @Vch_SqlString + ' WITH INIT , STATS = 10 '

--SELECT @Vch_SqlString

EXECUTE(@Vch_SqlString)

declare @cmd Varchar(1000)

declare @deldate datetime

select @deldate = getdate()- 15

WHILE @deldate <= (getdate()-@FileDelDays)

BEGIN

select @cmd = 'del '+@BackupPath+replace (@@servername, '\', '$')+'_'+@Sys_Database+'_tlog_'+convert(char(8), @deldate , 112)+'*.trn /S ' --add this for only archives to delete...

EXEC master.dbo.xp_cmdshell @cmd, no_output

select @cmd = 'del '+@BackupPath+'Completed\'+replace (@@servername, '\', '$')+'_'+@Sys_Database+'_tlog_'+convert(char(8), @deldate , 112)+'*.trn /S '

EXEC master.dbo.xp_cmdshell @cmd, no_output

select @deldate = @deldate+1

END

end

GO

MohammedU
Microsoft SQL Server MVP

pinky SSCommitted Points: 1997 More actions · Answer 12

Hi Mohammed Thanks for your code.

Can you please let me know if it is easy to maintain tlog backups if we create it via the Maintenance plan wizard? Does it create separate tlog files each time the job runs. Please let me know.

Thanks

Tom Goltl SSCertifiable Points: 5755 More actions · Answer 13

Why do you want to create a bunch of individual files. One file would have everything you would need to restore the database to a point in time. If you loose/delete on of the individual files your out everything. Management is harder and payback is small for the amount of administration. Just trying to understand the logic to it all.

Tom

pinky SSCommitted Points: 1997 More actions · Answer 14

i dont have a clear idea on this. i was thinking that we need to apply all the individual files at the time time of restore. so do i need to append the data for these transactional log backups? please let me know.

If i do it through the wizard will it do everything by itself?

Thanks for ur reply