Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I was once told that running manual backups of SQL server databases from Managament Studio was a big no-no on production databases as it can interfere with the automated backup jobs on the server. From memory I think I was told that it results in the NEXT automated backup job only backing up transaction log entries since the manual backup was made...NOT since the last automated backup, meaning that transaction log entries between the last automated backup and the manaual backup are effectively lost (or not backed up to the usual backup store)?

I am not a SQL admin (obviously) so I may be totally off here. Is this even remotely accurate?

Thanks

share|improve this question

1 Answer 1

up vote 2 down vote accepted

That is correct, sort of. If you are doing differential backups it's a problem. The reason is that when you do a full backup all the differential flags are reset so any differential backups taken between your one off backup and the next normal full backup are based off of your one off backup.

If you need to take a one off backup, use the copy_only flag (there's a check box on the options page in SSMS) to take a backup without effecting the differential backups.

If you are not taking differential backups, and are only taking full and transaction log backups then this is BS as full backups have no impact on transaction log backups.

share|improve this answer
1  
Mostly accurate, but differential / incremental backups are transaction log backups. Truncating the logs afterwards in case of incrementals or not truncating them in case of differentials. BTW: there is a notion of the "recovery model" in MS SQL Server - "simple" recovery model only allows for full data backups as transaction logs are truncated frequently anyway and backing them up would not be any good. Only the "full" recovery model allows for incremental / differential transaction log backup schemes. –  the-wabbit Mar 20 '12 at 8:23
    
Nope, not even close. Differential backups have nothing to do with the transaction log. Every time an extent in the database is updated the differential bitmap is updated marking that extent as changes (value changed from 0 to 1). When a differential backup is going to be run the SQL Server copies all the extents which have their differential bit set to 1 to the backup file. When the next full backup is run the differential bit is set to 0 for all the extents in the database. Differential backups can be taken against any of the three recovery models. –  mrdenny Mar 21 '12 at 0:56
    
Oh, and SQL Server has no concept of incremental backups. We have full backups, differential backups and transaction log backups. –  mrdenny Mar 21 '12 at 0:58
    
Marking as answer as it seems to be inline with info I have found since. Thanks. –  MrLane Apr 9 at 6:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.