COPY_ONLY Backups in SQL Server Rarely Used Feature by DBAs
July 15, 2012
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”
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.
4. In Select Backup Destination dialog box; click “…” button to open up Locate Database Files dialog box.
Click on Next Page to continue reading rest of the article…
Continue Free Learning...