Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - 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

ċ
Backup one or many databases with COPY_ONLY.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:53 AM
Comments