Backup all Databases with COPY_ONLY
Applicability:
SQL Server 2000: not supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
Credits:
Author: ChillyDBA
Date: Jan 2012
Description
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.
Code
-- backup all databases with COPY_ONLY
DECLARE
@DBName SYSNAME,
@Command NVARCHAR(MAX),
@Folder NVARCHAR(MAX),
@DBFilter NVARCHAR(MAX)
SELECT
@Folder = '<backup folder>',
@DBFilter = '%%'
-- loop through all the DBs that match the filter criteria
-- get the first DB
SELECT @DBName = MIN(Name )
FROM master..sysdatabases
WHERE Name LIKE @DBFilter
WHILE @DBName IS NOT NULL
BEGIN
-- create the backup command
SELECT @Command = ''
SELECT @Command = @Command
+ 'BACKUP DATABASE ['
+ @DBName
+ '] TO DISK = '''
+ @Folder
+ REPLACE(@DBName, ' ', '')
+ '.bak'''
+ ' WITH COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 1'
-- execute the backup command
--SELECT @DBName, @Command
EXEC (@Command)
-- get the next DB
SELECT @DBName = MIN(Name )
FROM master..sysdatabases
WHERE Name LIKE @DBFilter
AND Name > @DBName
END