Sunday, December 5, 2010

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.


copy only printscreen




And for those who like code:


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