How to take a SQL Server backup without breaking the backup sequence

When a SQL Server is configured to run scheduled full backups followed by a sequence of differential backups, taking an ad hoc backup will affect how backups can be restored, potentially breaking the ability to restore to particular points in the sequence before the ad hoc backup was taken. If a backup file is required, for example as a simple way of copying the database to another server, the solution is to take a “copy-only” backup.

This is achieved by ticking the “Copy-only Backup” checkbox, and setting an appropriate path for your backup file, as shown here in SQL Server 2008 Management Studio:

SQL Server copy-only backup

It can also be achieved using command-line T-SQL.

More information and explanation here from Microsoft.


Find out more