January 7, 2016 at 12:13 am
Hey
Is it possible that i take a compressed backup of a database every week where 3rd week's bakup would overwrite1st week's backup and then 4th week's backup would overwrite 2nd week's and so on..........
January 7, 2016 at 5:20 am
Yes, but you'll have to do some smart scripting to pull it off. The trick would be to have the file name for the backup generated such that it will cycle through three names.
A much better idea would be to download and use Ola Hallengren's maintenance scripts. It will not reuse existing backup files, but it will automatically delete old backup files after a preset period. So if you set that period to 21 days and the backup frequency to weekly (which I consider to be quite low, BTW), the you will get the same net effect. (Note that the backup is taken before the old one is deleted; if you don't want that for disk space reasons you will have to go in and edit the script).
January 7, 2016 at 5:47 am
Agreeing with Hugo here; certainly possible,it would just take a naming convention to do this.
I would think it would be a LOT easier just to name each backup by the date of the backup,and just delete backups older than X number of days instead. As Hugo identified, Ola's scripts have that built in, and I do something like that with a powershell script as a job step to handle deletes on remote file locations, but there's more than one way to do that.
Lowell
January 7, 2016 at 8:15 am
I'm with Hugo and Lowell. I'll just offer an alternative to using Ola's scripts (nothing wrong with them, I just like choices). You could check out MinionWare[/url]. They have a backup script that will do what you're asking for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2016 at 8:39 am
I agree on staying with tested solutions. However, I just wanted to leave an idea on how it can be done by using a starting date (I chose the first Sunday of this year). You wouldn't need the CTE, it's just there to simulate the different weeks.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP 52 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4
),
Weeks(GetSunday) AS(
SELECT DATEADD( WK, n, '20160102')
FROM cteTally
)
SELECT 'BACKUP DATABASE AdventureWorks2012 TO DISK = ''Z:\SQLServerBackups\'
+ CASE WHEN DATEDIFF( WK, '20160102', GetSunday) % 3 = 0 THEN 'AdvWorksData1'
WHEN DATEDIFF( WK, '20160102', GetSunday) % 3 = 1 THEN 'AdvWorksData2'
WHEN DATEDIFF( WK, '20160102', GetSunday) % 3 = 2 THEN 'AdvWorksData3'
END
+ '.bak'' WITH FORMAT;',
GetSunday
FROM Weeks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply