We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileNameAS VARCHAR(200)
DECLARE @.HoursAS CHAR(02)
DECLARE @.MinsAS CHAR(02)
DECLARE @.SecsAS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTimeAS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.
Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileNameAS VARCHAR(200)
> DECLARE @.HoursAS CHAR(02)
> DECLARE @.MinsAS CHAR(02)
> DECLARE @.SecsAS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTimeAS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins= substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs= substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.
Showing posts with label hourly. Show all posts
Showing posts with label hourly. Show all posts
Thursday, March 22, 2012
Backup Script Revisions Help
We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although th
e
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script
is
> running well, but the old backups are not deleted from the disk, although
the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script t
o
> ensure that backups are deleted successfully? Thanks in advance for your
> help.
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although th
e
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script
is
> running well, but the old backups are not deleted from the disk, although
the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script t
o
> ensure that backups are deleted successfully? Thanks in advance for your
> help.
Backup Script Revisions Help
We recently made some changes to our hourly transaction log backups script
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.sql
that allows us to backup the logs more than once an hour. The new script is
running well, but the old backups are not deleted from the disk, although the
retaindays option is set to 0.
I have done some research on this, but I cannot find an answer. I am sure
this is not a permissions issue, as the script does run successfully.
New Script (successful, but old backups are not deleted):
DECLARE @.FileName AS VARCHAR(200)
DECLARE @.Hours AS CHAR(02)
DECLARE @.Mins AS CHAR(02)
DECLARE @.Secs AS CHAR(02)
DECLARE @.HoursMins AS VARCHAR(40)
DECLARE @.ScriptTime AS DATETIME
SET @.ScriptTime = GETDATE()
-- Determine if time value is < 2 digits
SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
-- PRINT @.Hours
-- PRINT @.Mins
-- PRINT @.Secs
-- PRINT convert(varchar, @.ScriptTime, 108)
SET @.HoursMins = @.Hours + @.Mins + @.Secs
-- PRINT @.HoursMins
SET @.FileName ='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
-- PRINT @.FileName
Old Script (backups were deleted successfully)
declare @.FileName1 as varchar(100)
set @.FileName1='P:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
getdate(),108),2)+ '.bak'
backup log DatabaseA
to disk =@.FileName1
WITH RETAINDAYS = 0
Can someone please give me some guidance on how to modify our new script to
ensure that backups are deleted successfully? Thanks in advance for your
help.Try using NOINIT instead of RETAINDAYS.
"Wen" wrote:
> We recently made some changes to our hourly transaction log backups script
> that allows us to backup the logs more than once an hour. The new script is
> running well, but the old backups are not deleted from the disk, although the
> retaindays option is set to 0.
> I have done some research on this, but I cannot find an answer. I am sure
> this is not a permissions issue, as the script does run successfully.
> New Script (successful, but old backups are not deleted):
> DECLARE @.FileName AS VARCHAR(200)
> DECLARE @.Hours AS CHAR(02)
> DECLARE @.Mins AS CHAR(02)
> DECLARE @.Secs AS CHAR(02)
> DECLARE @.HoursMins AS VARCHAR(40)
> DECLARE @.ScriptTime AS DATETIME
> SET @.ScriptTime = GETDATE()
> -- Determine if time value is < 2 digits
> SET @.Hours = substring(convert(varchar, @.ScriptTime, 108), 1, 2)
> SET @.Mins = substring(convert(varchar, @.ScriptTime, 108), 4, 2)
> SET @.Secs = substring(convert(varchar, @.ScriptTime, 108), 7, 2)
> -- PRINT @.Hours
> -- PRINT @.Mins
> -- PRINT @.Secs
> -- PRINT convert(varchar, @.ScriptTime, 108)
> SET @.HoursMins = @.Hours + @.Mins + @.Secs
> -- PRINT @.HoursMins
> SET @.FileName ='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log_'+ @.HoursMins + '.bak'
> BACKUP LOG DatabaseA TO DISK = @.FileName WITH RETAINDAYS = 0, STATS = 10
> -- PRINT @.FileName
>
> Old Script (backups were deleted successfully)
> declare @.FileName1 as varchar(100)
> set @.FileName1='P:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\DatabaseA_Log'+ left(CONVERT ( varchar ,
> getdate(),108),2)+ '.bak'
> backup log DatabaseA
> to disk =@.FileName1
> WITH RETAINDAYS = 0
> Can someone please give me some guidance on how to modify our new script to
> ensure that backups are deleted successfully? Thanks in advance for your
> help.sql
Thursday, March 8, 2012
Backup Options - low disk space
Howdy,
We have very a tight limit on disk space for backups.
We have a 3 GB db that we do full a weekly full backup on, followed by hourly tran log backups for the rest of the week - this adds up to a LOT of disk space usage ( we cant do differential backups due to space limitations, as diffs are almost 3 GB in size... ).
As disk space is limited, we can only keep a weeks worth of backups on the disk, and previous weeks get backed up onto tape. No big deal there....
So, when weekly full backup occurs, the only way to recover the previous weeks data is to restore previous full backup & allpy the required number of tran logs.
Is there another way to do backups perhaps using file backups , so we dont have to go all the way back to the previous weeks backups?
Thanks in advance,
SG.Do you have a tape device on the machine? If so you could copy the backup off every night and run a full backup every night...if this is an important server, buy more disks!! I bought an external 80gb drive for $90, I would push back and have the company buy more space.
HTH|||Hi,
Thanks - I have suggested to the clients the disk space option is the best way of going, I just wanted someone else to say it too. I figure its not worth the grief for skimping on disk space for the sake of operational integrity.
Cheers
SG|||Hi,
If you haven't yet bought hard disk, you could do this. You could schedule the full backups daily and 'overwrite the existing media' instead of appending. Also you can delete the transactional log after the successful backup at the end of the day.
You could continue with the weekly tape backups too. If you need to restore, you need to the full with no recovery and the tran log for just the previous day. Ofcourse if the restoration is beyond a days time, you will need to use the tape, previous days full backup and transaction logs.
Hope this help you.
Regards,
We have very a tight limit on disk space for backups.
We have a 3 GB db that we do full a weekly full backup on, followed by hourly tran log backups for the rest of the week - this adds up to a LOT of disk space usage ( we cant do differential backups due to space limitations, as diffs are almost 3 GB in size... ).
As disk space is limited, we can only keep a weeks worth of backups on the disk, and previous weeks get backed up onto tape. No big deal there....
So, when weekly full backup occurs, the only way to recover the previous weeks data is to restore previous full backup & allpy the required number of tran logs.
Is there another way to do backups perhaps using file backups , so we dont have to go all the way back to the previous weeks backups?
Thanks in advance,
SG.Do you have a tape device on the machine? If so you could copy the backup off every night and run a full backup every night...if this is an important server, buy more disks!! I bought an external 80gb drive for $90, I would push back and have the company buy more space.
HTH|||Hi,
Thanks - I have suggested to the clients the disk space option is the best way of going, I just wanted someone else to say it too. I figure its not worth the grief for skimping on disk space for the sake of operational integrity.
Cheers
SG|||Hi,
If you haven't yet bought hard disk, you could do this. You could schedule the full backups daily and 'overwrite the existing media' instead of appending. Also you can delete the transactional log after the successful backup at the end of the day.
You could continue with the weekly tape backups too. If you need to restore, you need to the full with no recovery and the tran log for just the previous day. Ofcourse if the restoration is beyond a days time, you will need to use the tape, previous days full backup and transaction logs.
Hope this help you.
Regards,
Subscribe to:
Posts (Atom)