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

Page updated

Google Sites

Report abuse