SQL Backup without interrupting the backup set

If you're like me, you're probably not too happy to make changes to a production database without a proper backup. 


Most SQL configurations I've seen either have replication/mirroring or they have a full backup once in a while and a differential backup relatively often so backing up the database normally will interrupt the flow of full to differential backups, creating a hole in the middle where your differential backups are unusable.


So what do we do?


Well, that's why "Copy Only" was made.

And for those who like code:

BACKUP DATABASE dbname
TO DISK = 'c:\folder\dbname.bak'
WITH COPY_ONLY
GO

Tags: