[SQL Snacks Video] Importance of COPY_ONLY Backups

There are many features/options we sometimes overlook and then wonder later what went wrong. The COPY_ONLY option with backups is one that I felt is important to highlight to SQL Server DBAs. This backup is independent backup that is not part of the regular cycled backups that you perform; hint if you’re not performing regular backups please get up and schedule them NOW! Sorry for yelling 🙂

Using this option when doing backups allows you to take backups that do not interfere with your regularly scheduled backups in order to move them off to a QA, Development, or Staging area where you can test against that database or fix bugs without interrupting your production environment. Many times, I have seen that off-cycle backups are taken which become part of the backup set and then are deleted. This can cause negative consequences when doing restores as I will demonstrate in today’s SQL Snack:

Code is provided below if you would like to test it yourself. Please watch the video in order to understand how to test this yourself:

****Remember to change the paths to match your environment****


--BACKUP Portion

Create Database BACKUPTEST

Backup Database BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTEST1.bak'
WITH COMPRESSION

Backup LOG BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTEST1.trn'
WITH COMPRESSION

Backup Database BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTESTDIFF1.bak'
WITH DIFFERENTIAL,COMPRESSION

---ANOTHER FULL BACKUP
---First time we will use COPY_ONLY OPTION
---Second time we will not use COPY_ONLY Option
Backup Database BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTEST2-NONSTANDARDbak'
WITH COMPRESSION--, COPY_ONLY

Backup LOG BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTEST2.trn'
WITH COMPRESSION

Backup LOG BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTEST3.trn'
WITH COMPRESSION

Backup Database BACKUPTEST
TO DISK = 'X:\SQLBACKUP\BACKUPTESTDIFF2.bak'
WITH DIFFERENTIAL,COMPRESSION

DROP Database BACKUPTEST

--RECOVERY Portion
USE [master]
RESTORE DATABASE [BACKUPTEST] FROM
DISK = N'X:\SQLBACKUP\BACKUPTEST1.bak' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5

GO

---Using COPY_ONLY we only need to restore this backup along with the FULL
USE [master]
RESTORE DATABASE [BACKUPTEST] FROM
DISK = N'X:\SQLBACKUP\BACKUPTESTDIFF2.bak' WITH FILE = 1,
NOUNLOAD, STATS = 5

GO

USE [master]
RESTORE DATABASE [BACKUPTEST] FROM
DISK = N'X:\SQLBACKUP\BACKUPTESTDIFF1.bak' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5

GO

USE [master]
RESTORE LOG [BACKUPTEST] FROM
DISK = N'X:\SQLBACKUP\BACKUPTEST1.trn' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5

GO

-- WILL be needed for without COPY_ONLY recovery
USE [master]
RESTORE LOG [BACKUPTEST] FROM
DISK = N'X:\SQLBACKUP\BACKUPTEST2.trn'
WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 5

GO

USE [master]
RESTORE LOG [BACKUPTEST] FROM
DISK = N'X:\SQLBACKUP\BACKUPTEST3.trn'
WITH FILE = 1,
NOUNLOAD, STATS = 5

GO