How to do a Copy Only Backup of a SQL Server Database

What do you do when the QA team requests a new copy of a database? Hopefully you've got a fairly recently full backup that you can use, but sometimes they will want one that is up to date. To provide that you can just run a full backup which becomes part of the restore chain or you can do a "copy only" backup which can be used and deleted without affecting your ability to restore on the main server in any way.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

James on 7/14/2010

Nice and easy! :-)

Karthi on 7/14/2010

simple and clear

srradha krishna on 7/14/2010

very nice

Charlie Bruno on 7/14/2010

I learned something new. Excellent topic.

adalberto on 7/14/2010

I do like

dfortier on 7/14/2010

Hi Andy, As usual, great video, keep them coming. Regards, Don

A86022A9FD on 7/14/2010

The information about not adding a backup to the restore chain during this procedure was very helpful.

Kristeen Sellers on 7/14/2010

Cody have interesting applications

Leonard Peoples on 7/14/2010

Great tip. Thanks.

Richard Presley on 7/14/2010

Always good to see a review of some of the basic stuff and the reasoning behind it. You guys are very good. Keep it up.

Don Weigend on 7/14/2010

Copy only backups will be very handy in our environment, thanks for the tip!

John Barone on 7/14/2010

Thank you! I learned something new and useful today.

David on 7/14/2010

Content is great. Video quality is poor.

Richard Ray on 7/14/2010

I'd like to see the script version to include in jobs. I can figure it out, but it would only take a minute to include it.

rungsree on 7/14/2010

cool, very nice trick

Mohamed Irshad on 7/14/2010

something new. excellent precise presentation.

Ravi Raj on 7/14/2010

excellent article

Neil Burnett on 7/15/2010

Need more info on the "Backup Chain" and why it is important to preserve it.

karthik on 7/17/2010

Cool

32DE12EB89 on 7/17/2010

Interesting and good technique - thanks.

Carlos on 7/17/2010

Excellent! Didn't know that!

Guy Tasmowski on 7/20/2010

Dear Sir Which version of SqlServer2005 are you using ? When I use the backupwizard the button 'copy only backup' does not appear on the scrteen. Is it possible to obtain the same result with a transact command ? Many thanks in advance for your feedback Yours faithfully Guy Tasmowski G.Tasmowski@quality-research.be For your info -- hereunder copy of my version. Microsoft SQL Server Management Studio 9.00.4035.00 Microsoft Analysis Services Client Tools 2005.090.4035.00 Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 8.0.6001.18702 Microsoft .NET Framework 2.0.50727.3603 Operating System 5.1.2600

Talha on 7/20/2010

I am sorry but i got confused. i will watch it again. What i get from this is that it wont let you restore unless there is a a fullbackup taken. So you tricked it by saving backup somewhere else and then restored the spicific interval.

prashant on 7/22/2010

Nice Information

CVNRAO on 4/26/2011

Really is very good .