Backup Strategy / Backup the Backup

Question

Post reply

Backup Strategy / Backup the Backup

2Tall

SSChampion

Points: 14644
More actions
November 21, 2008 at 1:56 am

#201650

Hi. I have a job that backs up:
Master / Model / msdb / ReportServer / ApplicationDB on a daily basis.
I have a second job that cleans up the backup files (older than 2 weeks).
I then use Symantec Backup Exec to backup the backups (.bak).
My problem. Symantec Backup exec backs up all the contents of the backup folder and my tape can no longer handle the volume.
Can anyone advise what they do if in a similar position?
Any scripts I can use to move older copies to say archive_backup then leave the most recent in the backup location?
Should I create a second job to backup the system databases on a monthly basis? I back them up nighlty as they are not that large...
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

If you configure BackupExec to make incremental backups it will only write the new backup files to tape.

I also wonder why you need to keep 2 weeks of backups locally.

Of course you can write a script to copy older files to a different location and when exclude that folder from your BackuExec backup.

But for this you either have to create a scheduled task in windows or use xp_cmdshell in a SQL job. Remember by default xp_cmdshell is disabled in SQL 2005.

[font="Verdana"]Markus Bohse[/font]

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 2

Hi,

When you are taking backup, overwrite the full backup to same .bak file... Then you will not have two backup files. Will it help?

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

2Tall SSChampion Points: 14644 More actions · Answer 3

Thanks for the replies. I keep 2 weeks worth as storage is not an issue at the moment.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

Philip Horan (11/21/2008)
Thanks for the replies. I keep 2 weeks worth as storage is not an issue at the moment.
Thanks,
Phil.

Storage might not be an issue on your disk, but it is on your tape.

[font="Verdana"]Markus Bohse[/font]

krayknot SSCertifiable Points: 7998 More actions · Answer 5

Philip Horan (11/21/2008)
Hi. I have a job that backs up:
Master / Model / msdb / ReportServer / ApplicationDB on a daily basis.
I have a second job that cleans up the backup files (older than 2 weeks).
I then use Symantec Backup Exec to backup the backups (.bak).
My problem. Symantec Backup exec backs up all the contents of the backup folder and my tape can no longer handle the volume.
Can anyone advise what they do if in a similar position?
Any scripts I can use to move older copies to say archive_backup then leave the most recent in the backup location?
Should I create a second job to backup the system databases on a monthly basis? I back them up nighlty as they are not that large...
Many Thanks,
Phil.

can i know why are you taking the backup of backup files ??

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

2Tall SSChampion Points: 14644 More actions · Answer 6

I did not purchase the SQL Agent for Symanatec.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Just keep 1 day of backups on your server, and write to tape every night, so you will have them all on tape. Can you zip your .bak files ?

2Tall SSChampion Points: 14644 More actions · Answer 8

Thanks guys.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jerry Hung SSChampion Points: 12968 More actions · Answer 9

It's only a matter of time before disk spaces run out, and then tape

2 ways

1. Increase storage space (buy bigger disk, tape)

2. Decrease storage used (compress, shorter data retention, etc...)

e.g.

FULL backup + DIFF backup + TLOG backup instead of FULL daily

Compress the native BAK file

Keep 1~2 days of FULL backup only, not a week or 2 weeks even

Red Gate SQL Backup does compression around 10~20% of original space used (100GB down to 20GB)

SQLServerNewbieMCITP: Database Administrator SQL Server 2005