Backup all Databases with COPY_ONLY
SQL Server 2000: not supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
Date: Jan 2012
When a SQL Server backup is taken, the log sequence number (LSN) for the database (stored in the msdb backup metadata) is updated. This is done regardless of whether the backup is SQL native, third party software, manually initiated or automated.
Occasionally, there is a need to take a backup of production data outside of the normal backup sequence.
While this has always been easy and mostly free of performance restrictions, there is always the issue of interfering with the LSN sequence, especially if the backup software is a centralised enterpise level tool whch may be outside of DBA control.
The release of SQL Server 2005 saw the introduction of a new COPY_ONLY option to the SQL server native backup command. This allows a full backup to be taken of the database, but without the post-backup updating of LSN. Effectively this produces a pont-in-time backup snapshot of the database.
The one drawback of this option on its initial release was that it could not be selected via the GUI, so the backups needed to be scripted. I find that I am increasingly using this option when starting a new contract, as it allows me to make a 'sandbox' copy of key prod databases which I can then investigate offline. Especially important when the customer has no formal development environment - very common in smaller government departments, as is the continued use of SQL Server 2005.
This script dynamically generates backup commands for some or all databases on a server and runs them.
A backup location must be supplied along with an optional database name filter.