Trending SQL Server Tips

COPY_ONLY Backups in SQL Server Rarely Used Feature by DBAs


July 15, 2012
Page: 1/3

Introduction

In this article we will take a look at COPY_ONLY Database Backup option which is available in SQL Server 2005 and higher versions. This feature is applicable across all the editions of SQL Server 2005 and higher versions.

"This article has a total of 3 pages including this page. Click the links below to open all 3 pages in a seperate window to learn How to use COPY_ONLY Backup Feature in SQL Server. Page 1, Page 2, Page 3"

Different Ways to Create COPY_ONLY Backups in SQL Server

In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.

  • Create COPY_ONLY Database Backups in SQL Server Using SQL Server Management Studio
  • Create COPY_ONLY Database Backups in SQL Server Using TSQL Script

Permissions Required to Take Database Backup in SQL Server

In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role. For more information with respect to Permissions refer the following article “How to Backup Database in SQL Server

Highly Recommended:- As a DBA your "TOP PRIORITY" should be to make sure your database backups are useful in case of any Disaster and should should know how to recover your database from a Disaster. Learn Different Types of Database Backup Options Supported in SQL Server and How to Fix Database Corruption Issues in SQL Server.

Create COPY_ONLY Database Backups in SQL Server Using SQL Server Management Studio

1. Open SQL Server Management Studio and connect to the appropriate instance of Microsoft SQL Server Database Engine in Object Explorer.

2. Right click Database -> Tasks  -> Back Up… option as shown in the snippet below. A COPY_ONLY backup option can be used to take a Full, Differential or Transaction Log backups without breaking the current database restore sequence. For more information with respect to backups supports for a database refer the following article titled “Overview of SQL Server Recovery Models

How to backup Database Using COPY ONLY Back up Feature in SQL Server

3. In Back Up Database Dialog box; select the checkbox “Copy-only Backup” as highlighted in the snippet below and then click on Add… button to Select Backup Destination.

Select COPY-ONLY Backup option in SQL Server Management Studio

4. In Select Backup Destination dialog box; click “” button to open up Locate Database Files dialog box.

Select Backup Destination in SQL Server Management Studio

Click on Next Page to continue reading rest of the article…

Next Page..

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."