July 7, 2016 at 4:18 pm
Hello,
today i get a weird issue, there is an sql agent job that has an SSIS package, that does a couple of things to the DB, then backs it up .bak, then sends it to ftp site.
we are on SQL server 2008 R2, the recovery model is simple, the database is 700mb, but when this ssis package runs and finishes and backs it up, the backup file .bak is 3 gigs!!
so the question before you guys is... can there be a looot of transactions happening in the SSIS package before the backup, which would allow the transaction log to grow, therefore causing it to become 3 gigs, even though its in simple mode? Also, i did it manually using the same script to back it up, and it backed up the database to 350 mb, and we are compressing the backup as well, the script is below, any ideas, maybe an option i can add?
BACKUP DATABASE [DirectMail_TMT ] TO DISK = N'\\someplace\Child_Directory\Database_Name.bak'
WITH NOFORMAT, NOINIT, NAME = N'Database_Name -Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION
thanks in advanced
July 8, 2016 at 2:52 am
Are you appending multiple backups to the same file?
With the options there (NOINIT), if that backup file already exists, SQL appends the backup to the existing file, resulting in a file with multiple backups in it.
I don't recommend doing that, it makes restores more complex and means if the file gets damaged you lose ALL the backups, not 1. Rather build up the backup file name to include a timestamp and back up to a new file each time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply