November 30, 2005 at 2:50 pm
Something like this may have been asked, so I appologize in advance.
I'm trying to backup a file every night, and need to keep copies of it for 7 days. Also, we need to keep one of those files for 3 months. Can anyone give me any pointers as to how to acheive this the most efficient way?
thanks
November 30, 2005 at 2:59 pm
I had to do something similar. I created a DTS package, that had some VBScript in it. The VBScript would pick up the last full backup and copy it to another location. A bit more VBScript looked for files older than 6 months in our case and deleted them.
However, you'll want to do some testing to ensure you can copy the backup files over the network at a decent speed.
Clive
November 30, 2005 at 2:59 pm
declare @sSQLMaintSwitchString nvarchar(4000)
set @sSQLMaintSwitchString =
N' -D "DatabaseName"' +
N' -BkUpMedia DISK -BkUpLog "\\BackupPath\DatabaseName "' +
N' -BkExt "BAK"' +
N' -DelBkUps 7DAYS' +
N' -Rpt "\\JobLogPath\Full_Backup_ServerName_DatabaseName.txt"' +
N' -DelTxtRpt 7DAYS' +
N' -WriteHistory' +
N' -VrfyBackup'
exec master..xp_sqlmaint @sSQLMaintSwitchString
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
December 1, 2005 at 3:38 pm
We ended up implementing a standard set of backup jobs across our enterprise. The jobs will backup the database with a unique date in the file name, delete backups older than x days, and then verify the backup. The backups goto a network share that a backup software picks up every night.
This is pretty flexible and has worked well, if different systems need more backups on hand you can increase the number of days the delete procedure will look back.
December 1, 2005 at 3:45 pm
well, in the maintenance plan, I'm already specifying to retain the files for 7 days. And I don't wanna have 3 month supply of nightly backups. Don't wanna have 90+ backup files.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply