SQL Server Copy Only Backup

Hi Friends,

Using SQL Server Copy Only Backup option for ad hoc backups.

Problem:

We always setup a bulletproof backup strategy for our production server using Full, Differential and transaction Log backups. Which always

Suppose on our production server the backup strategy is as follows:

Full Backup: 1 AM (Sunday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

Differential Backup: 1 AM (Monday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup: 1 AM (Tuesday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup: 1 AM (Wednesday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup: 1 AM (Thursday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

DIfferential Backup : 1 AM (Friday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

Differential Backup: 1 AM (Saturday 1 AM)

TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM…………..

But suppose if I need an ad hoc database full backup at 11:30 AM on Wednesday to troubleshoot some issue.

Now suppose if I take normal full backup at 11:30 AM then Backup chain will be breakup, because differential backup contain data modified after the full backup.

Here, I just want to take the backup of database as a single file without the breakup of Backup chain or without knowing the complexity of Production server backup strategy.

Solution:

To solve this problem SQL Server provides a special type of Backup Option – “Copy Only Backup”

This feature was introduced in SQL Server 2005 but it is also work in SQL Server 2005 and 2008, if your database compatibility level is 80. It never breaks your backup chain.

   

We can explain Copy Only Backup as “Copy only backup is a type of backups in SQL Server, which is mostly used for taking ad hoc backup and never”

In SQL Server 2005:

(a)    Only through TSQL as:

Backup Database TEST to Disk=’D:\TEST_FULL.bak’ WITH COPY_ONLY

In SQL Server 2008 and 2008 R2:

(a)    Using TSQL as :

Backup Database TEST to Disk=’D:\TEST_FULL.bak’ WITH COPY_ONLY

(b)    Using SSMS :

When you taking Full Database Backup from SSMS, then to make it copy only just click on check box option as shown below.

1_SQL_Server_Copy_Only_Backup

 

Regards

Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.