Will Trans. logs backup options be in the wizard in sp2?
At this point there are no options to backup trans. logs every 1hr and deleting files over x amount of days old without doing a TSQL command correct?
To clarify, in 2k enterprise, it's in the maintenance plan. I can specify that info and give it the trn extension and folder to backup into.
I am a network admin being given sql duties now, so If I may seem like a beginner, it's because I am.
I did to a search for this topic, so forgive me if there is one i did not find.
Also, I was trying to submit feedback as requested for sp2 about this, but i did not see a button on the bottom of the search through feedback as stated.
You can do transaction log backups along with differential backups in a maintenance plan. You have to add a Backup database task. Then you can configure the backup type within the task. (It's the third field from the top in the dialog.)|||Probably looking for a similar option ion SQL Server 2000. I had a hard time myself way back.|||
You can back up Transaction logs every hour if you want to, just set up a job to back them up as often as you wish, for example:
http://img.photobucket.com/albums/v472/Schmedrick/backuptranslog.gif
You can also delete backup files older than "x" days by adding a maintenance cleanup task to your maintenance plan after it has been created, this does require that SP1 has been installed, for example:
http://img.photobucket.com/albums/v472/Schmedrick/mainttask.gif
|||
Is there a way to run hourly transaction log backups while backing up the database once a day in ONE maintenance plan (like SQL2000)? Or do we have to create one maintenance plan for the full backup and another one for the transaction log backups.
|||No. A maintenance plan is generated as a single monolithic SSIS package. That means the entire package has a single schedule. If you have multiple tasks in the maintenance plan, they will all run on the same schedule. So, in 2005, you have to create a separate maintenance plan for each group of tasks that need to have a distinct schedule. It is for this reason, that I do not recommend using maintenance plans at all and simply writing the code for this stuff yourself. I also don't like the fact that I have to enlist the SSIS engine, so now running my backups has a dependency on the SSIS engine as well as the security access of the SSIS engine. If something happens to SSIS, my backups no longer run. Can you tell that I absolutely hate the new maintenance plans in 2005? It takes something that was very simple and very straightforward and turned it into a mess than has to transit multiple subsystems in order to perform and VERY straightforward task.
Below is the set of code that I use for backing up databases along with a procedure to clean up the backup history and backup files. All T-SQL. Simple, straightforward, and it is smart enough to not try to issue a tran log backup against a database that is in simple recovery model. All you have to do is create the jobs to call the backup and clean-up based on the type of backup you want to run and the frequency you want it to run on.
CREATE TABLE [dbo].[backupfiles](
[DatabaseName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BackupFileName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreationDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE procedure [dbo].[asp_backup]
@.dir varchar(50), @.type char(1)
as
/****************************************************************/
/* Stored Procedure: asp_backup */
/* Creation Date: 4/01/2006 */
/* Written by: Mike Hotek - MHS Enterprises, Inc. */
/* http://www.mssqlserver.com */
/* Copyright: 2006 MHS Enterprises, Inc. All Rights Reserved */
/* Code can be used in its entirety, free of charge. Code can*/
/* not be incorporated into other products that are for sale. */
/* This copyright notice must remain intact when used. */
/* Code is provided as-is, without warranties. It is the */
/* user's sole responsibility to test this code for */
/* suitability in their environment. */
/* */
/* Purpose: Executes full, differential, and tran log backups */
/* */
/* Input Parameters: */
/* @.dir directory for the backup */
/* @.type backup type */
/* */
/* Output Parameters: None */
/* */
/* Return Status: 0 success */
/* -1 failure */
/* */
/* Usage: exec asp_backup 'c:\data','T' */
/* Executes a transaction log backup against each */
/* database that is in full recovery model and drops the */
/* backups in the directory c:\data */
/* */
/* Local Variables: */
/* */
/* Called By: SQL Server Agent job */
/* */
/* Calls: None */
/* */
/* Data Modifications: */
/* Inserts into the blockedprocess table */
/* */
/* Updates: */
/* Date Author Purpose */
/* 4/01/06 Mike Hotek Created */
/****************************************************************/
declare @.filename varchar(255),
@.database varchar(255),
@.recovery int,
@.command nvarchar(1000)
--Error checking
if @.dir is null
begin
print 'You must specify a directory path'
return
end
if @.type not in ('F','D','T')
begin
print 'Valid backup types are F, D, and T'
return
end
--Format directory path
if right(@.dir,1) != '\'
set @.dir = @.dir + '\'
set @.command = null
declare curdb cursor for
select name, status from master.dbo.sysdatabases where name not in ('tempdb','model')
for read only
open curdb
fetch curdb into @.database, @.recovery
while @.@.fetch_status = 0
begin
--Format filename. Functions needed to work around single digit month, day, minute, etc.
set @.filename = @.dir + @.database + '_' + cast(datepart(yyyy,getdate()) as char(4)) +
right('0' + rtrim(cast(datepart(mm,getdate()) as char(2))),2) +
right('0' + rtrim(cast(datepart(dd,getdate()) as char(2))),2) +
right('0' + rtrim(cast(datepart(hh,getdate()) as char(2))),2) +
right('0' + rtrim(cast(datepart(mi,getdate()) as char(2))),2) +
right('0' + rtrim(cast(datepart(ss,getdate()) as char(2))),2)
set @.filename = @.filename + case when @.type = 'F' then '_full.bak'
when @.type = 'D' then '_diff.bak'
else '.trn' end
--Format backup command
if @.type = 'F'
begin
set @.command = 'backup database [' + @.database + '] to disk = ''' + @.filename + ''' with init'
end
else if @.type = 'D' and @.database != 'master'
begin
set @.command = 'backup database [' + @.database + '] to disk = ''' + @.filename + ''' with differential, init'
end
else if @.type = 'T' and DATABASEPROPERTYEX(@.database, N'RECOVERY') = 'Full'
begin
set @.command = 'backup log [' + @.database + '] to disk = ''' + @.filename + ''' with init'
end
--Execute backup
if @.command is not null
begin
print @.command
exec sp_executesql @.command
end
insert into dbo.backupfiles
(DatabaseName, BackupFileName, CreationDate)
values(@.database, @.filename, getdate())
set @.command = null
fetch curdb into @.database, @.recovery
end
close curdb
deallocate curdb
GO
CREATE procedure [dbo].[asp_bakfilecleanup] @.backupretention int,
@.historyretention int
as
/****************************************************************/
/* Stored Procedure: asp_bakfilecleanup */
/* Creation Date: 4/01/2006 */
/* Written by: Mike Hotek - MHS Enterprises, Inc. */
/* http://www.mssqlserver.com */
/* Copyright: 2006 MHS Enterprises, Inc. All Rights Reserved */
/* Code can be used in its entirety, free of charge. Code can*/
/* not be incorporated into other products that are for sale. */
/* This copyright notice must remain intact when used. */
/* Code is provided as-is, without warranties. It is the */
/* user's sole responsibility to test this code for */
/* suitability in their environment. */
/* */
/* Purpose: Removes old backup files from the file system. */
/* */
/* Input Parameters: */
/* @.backupretention Number of days to retain backup files */
/* @.historyretention Number of days to retain backup history*/
/* */
/* Output Parameters: None */
/* */
/* Return Status: 0 success */
/* -1 failure */
/* */
/* Usage: exec asp_bakfilecleanup 3,15 */
/* Deletes any backup files older than 3 days and any */
/* backup history older than 15 days */
/* */
/* Local Variables: */
/* */
/* Called By: SQL Server Agent job */
/* */
/* Calls: None */
/* */
/* Data Modifications: */
/* Deletes rows from the backupfiles table */
/* */
/* Updates: */
/* Date Author Purpose */
/* 5/10/06 Mike Hotek Created */
/****************************************************************/
--This is wired to a logging table simply to avoid messy code in
--parsing file names or pulling fiel attributes to figure out which
--files to purge
declare @.file varchar(255),
@.command varchar(1000)
if @.backupretention is null
begin
print 'You must specify a retention interval'
return
end
if @.historyretention is null
begin
print 'You must specify a retention interval'
return
end
--Pull the list of files that are older than the retention
-- Construct a del command for each file and execute it.
declare curfile cursor for select BackupFileName from dbo.backupfiles
where CreationDate < dateadd(dd,-@.backupretention,getdate())
for read only
open curfile
fetch curfile into @.file
while @.@.fetch_status = 0
begin
set @.command = 'exec master.dbo.xp_cmdshell ''del ' + @.file + ''''
exec(@.command)
fetch curfile into @.file
end
--Clean up objects that were created
close curfile
deallocate curfile
delete from backupfiles
where CreationDate < dateadd(dd,-@.historyretention,getdate())
GO
