Wednesday, March 7, 2012

Overwriting backups

I'm backing up my transaction log on backup device every 15 minutes. I want
every backup expire in 1 hour so only 4 sequential transaction log backups
reside on my backup device. How do I do that? Thank you in advance
LeonYou can't if you are using a single device. It is all or nothing with your
only options being to use INIT or NOINIT. INIT will remove ALL files in the
device and NOINIT will simply append. Don't use a logical device and
instead backup to a different file name each time. Then you can delete what
you want.
--
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
> I'm backing up my transaction log on backup device every 15 minutes. I
> want
> every backup expire in 1 hour so only 4 sequential transaction log backups
> reside on my backup device. How do I do that? Thank you in advance
> Leon
>|||Thank you, Andrew, I hope this is something that is going to be addressed in
Yukon.
Thanks again for your help!
"Andrew J. Kelly" wrote:
> You can't if you are using a single device. It is all or nothing with your
> only options being to use INIT or NOINIT. INIT will remove ALL files in the
> device and NOINIT will simply append. Don't use a logical device and
> instead backup to a different file name each time. Then you can delete what
> you want.
> --
> Andrew J. Kelly SQL MVP
>
> "Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
> > I'm backing up my transaction log on backup device every 15 minutes. I
> > want
> > every backup expire in 1 hour so only 4 sequential transaction log backups
> > reside on my backup device. How do I do that? Thank you in advance
> >
> > Leon
> >
>
>|||Not that I know of. This isn't a bug it is simply the way it works. There
is alreay a viable way to handle this. That is to use multiple devices as I
already mentioned. Here is a simple example of how to do this with tsql:
-- Do a backup and create a separate file for each day of the
eek --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
----
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
----
-- *** Procedure to remove old backups **** --
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote in
message news:176BA8ED-9759-4D19-BD95-25086540C399@.microsoft.com...
> Thank you, Andrew, I hope this is something that is going to be addressed
> in
> Yukon.
> Thanks again for your help!
> "Andrew J. Kelly" wrote:
>> You can't if you are using a single device. It is all or nothing with
>> your
>> only options being to use INIT or NOINIT. INIT will remove ALL files in
>> the
>> device and NOINIT will simply append. Don't use a logical device and
>> instead backup to a different file name each time. Then you can delete
>> what
>> you want.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Leon Shargorodsky" <Leon Shargorodsky@.discussions.microsoft.com> wrote
>> in
>> message news:FA6D9E38-FD99-456D-93B0-7C3ED322E3E1@.microsoft.com...
>> > I'm backing up my transaction log on backup device every 15 minutes. I
>> > want
>> > every backup expire in 1 hour so only 4 sequential transaction log
>> > backups
>> > reside on my backup device. How do I do that? Thank you in advance
>> >
>> > Leon
>> >
>>

No comments:

Post a Comment