Transaction Log Backups
-
Hi There,
Is it possible to create a maintenance plan for transaction log backups as in SQL 2000 ? How would i do this in SQL Server Management Studio ? According to "help" you can create the transaction logs by adding it as a task, is there any other way to do this ? I've created the backup plan to run weekly and i would like to create transaction log backups to run daily every 4-6 hours or so.
Thanks,
Sonia
-
Very similar to how your crated your backup plan to run weekly just in the backup database task make sure the type to transaction logs and schedule accordingly.
Cheers
Stephen
-
Oh my i feel like a UBBER SQL2K5 NOOB
! Dunno how i overlooked that 
Thanks so much
-
I would create a SQL Agent job the generates you transaction log backups
hourly with the date embeded in the name of the backup file.
The Job should execute a stored procedure tht uses a cursor to
parse thru and generate the commands to generate the xact logs for
each datbase on the server.
The job should have two steps to generate the xact log backups.
1. Step 1 - Generate the sql code to generate the backups into a file.
2. Step 2 - Execute the file containing commands to generate the transaction logs.
Example of Job
Job Name: T_LOG_HOURLY_ BACKUPS
Category: Uncategorized
Owner: SA (or Login of your choice with sa prviliges)
Steps
Step 1 Name: T_Log_SP_Execution_STEP1
Step 1 Type: Operating System(CMDEXE)
Step 1 Code:
isqlw -Usa -Pxxxxxx -Sservername -ic:\util2\jobs\XACTdump.sql -oc:\util2\jobs\xact.sql
Step 2 Name: T_Log_SP_Execution_STEP2
Step 2 Type: Operating System (CMDEXE)
Step 2 Code:
isqlw -Usa -Pxxxxxx -Sservername -i c:\util2\jobs\XACT.sql -oc:\util2\jobs\xactdump.txt
File Name: XACTdump.sql
File Contents:
Exec sp_db_xactdmp
go
Stored Procedure to create transaction log dumps.
SET
QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS ON
go
CREATE PROC sp_db_xactdmp
AS
BEGIN
declare xact cursor
for select name from master..sysdatabases
where name not in ('master','model','tempdb','msdb') /* Put names of databases you don want to generate XACT log for */
begin tran
declare @time char(19),
@time2 char(8),
@dump_file varchar(150),
@dbname varchar(150)
open xact
select @time = convert(char(19),getdate(),0)
select @time2 = convert(char(8),getdate(),8)
fetch xact into @dbname
while @@FETCH_STATUS = 0
begin
if @@ERROR <> 0
begin
RAISERROR ('cursor fetch failed',16,1)
Rollback tran
return
end
select @dump_file ='Backup Log ' +
@dbname + ' ' +
'TO Disk =' + ' ' + "'"+'D:\dbbackups\logs\' + /* Location where you want to store you Xact Logs */
substring(@time,10,2) +
substring(@time,1,3) +
ltrim(substring(@time,5,2)) +
@dbname + '_' +
ltrim(substring(@time2,1,2)) + '.log''' +
' WITH NOINIT'
print @dump_file
fetch xact into @dbname
end
commit tran
close xact
deallocate xact
END
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply