Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 15, 2006

COPY_ONLY Backups in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

Let us assume that your production server resides in New York and your Disaster recovery site is 3000 miles away, say San Francisco. Let us also assume that your backup policy in production server is a nightly full backup and transaction log backup every 15 minutes and you have regular log shipping setup between your production server and standby server.

Let us say one of your clients in New York is requesting a fresh FULL backup right away. The following are the choices you have.

Choice 1

a.  Copy yesterday’s FULL backup and all the subsequent transaction log backups to a test server

b.  Restore full backup in recovery mode

c.  Restore all transaction log backups sequentially

d.  Restore the last transaction backup in NORECOVERY mode

e.  Take a full backup from the test server

f.   Zip the full backup

g.  FTP the zip file

All the above process are time consuming, with many manual steps involved.

Choice 2

a.  Take a fresh full backup from the production server

b.  Zip the full backup

c.  FTP the zip file

By doing so, we are forcing the standby server to synchronize the newly taken full backup. That basically involves:

a.  Copy full backup from production server to standby server across WAN

b.  Restore the full backup

Although this happens automatically, the latency increases and there is the potential risk of no DR for few hours.

Choice 3

a.  Bring the database online on the standby server

b.  Take a fresh backup

c.  Zip the full backup

d.  FTP the zip file from the Disaster recovery location

e.  Re-Synch the log shipping again

Choice 3 takes more time in FTPing from the DR site when compared to FTPing it from NY and there are manual steps involved. Re-synching involves restoring the full backup first.

Choice 4

a. Take a differential backup

b. Copy yesterdays full backup and the differential backup to a test sever.

c.  Restore the full backup in recovery mode

d.  Restore the differential backup in norecovery mode

e.  Take fresh backup

f.   Zip the full backup

g.  FTP the zip file from the Disaster recovery location.

h.  Re-Synch the log shipping again.

No matter which choice we select, we have to go through all this hassle just because the backup processes changes the database, which in turn affects the database backup and restore process.

In SQL Server 2005, Microsoft added the new feature, COPY_ONLY, which helps us in taking FULL Backup, transaction log backup and differential backup without affecting the backup / restore cycle, which means no changes to the archive log point. The transaction log backup with COPY_ONLY option does not truncate the transaction log and retains the current archive log point.

The following example demonstrates how the backup command, without COPY_ONLY option, affects the restore process.

Let us assume we have the following database and table.

use master
go
Create database MyTestDB
go
--At 11PM
Backup database MyTestDB to disk='d:\backup\MyTestDB_FULL.bak' with init
use MyTestDB
go
Create database MyTestDB
go
--At 11PM
Backup database MyTestDB to disk='d:\backup\MyTestDB_FULL.bak' with init
use MyTestDB
go
Create table MyTestTable
(id int, name varchar(100))
go
--Between 11:00 PM and 6:00 AM
insert into MyTestTable select 1,'Claire'
insert into MyTestTable select 2,'Ziyi'
insert into MyTestTable select 3,'Mizuho'
insert into MyTestTable select 4,'Catherine'
insert into MyTestTable select 5,'Jen'
go
--Between 6:00 AM and 7:00 AM
insert into MyTestTable select 11,'Sam'
--At 7:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_1.bak' 
--Between 7:00 AM and 8:00 AM
insert into MyTestTable select 12,'Carrie'
--At 8:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_2.bak' 
--Between 8:00 AM and 9:00 AM
insert into MyTestTable select 13,'Christine'
--At 9:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_3.bak' 
--Between 9:00 AM and 10:00 AM
insert into MyTestTable select 14,'Doug'
--At 10:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_4.bak' 
--Between 10:00 AM and 11:00 AM
insert into MyTestTable select 15,'Jackson'
--At 11:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_5.bak' 
--Between 11:00 AM and 11:29 AM
insert into MyTestTable select 115,'Lewis'
insert into MyTestTable select 119,'Scott'
--At 11:29AM
--Intermediate backup after clint request
BACKUP LOG MYTESTDB TO DISK='C:\MYTESTDB_NEWLOG_BACKUP.BAK' 
--Between 11:29 AM and 12:00PM
insert into MyTestTable select 129,'Tiger'
--At 12:00PM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_6.bak' 
--Between 12:00 PM and 1:00 PM
delete from MyTestTable where id =3
--At 1:00PM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_7.bak' 

Here, we can see that we take an additional transactional backup at 11:29AM.

Let us restore the full backup and all transaction log backups in sequence except the additional transactional log backup.

use master
go
restore database MyTestDB from disk ='d:\backup\MyTestDB_FULL.bak'  with replace, norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_1.bak'  with norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_2.bak'  with norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_3.bak'  with norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_4.bak'  with norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_5.bak'  with norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_6.bak'  with norecovery
go
restore log MyTestDB from disk ='d:\backup\MyTestDB_Log_7.bak'  with norecovery
go

When the above statements are executed, you will get the following error message.

Processed 152 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 2 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.370 seconds (3.390 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 4 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 4 pages in 0.082 seconds (0.374 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.092 seconds (0.005 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.116 seconds (0.004 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.170 seconds (0.003 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.056 seconds (0.009 MB/sec).
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 20000000012400001, 
    which is too recent to apply to the database. 
    An earlier log backup that includes LSN 20000000012200001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 20000000012500001, 
    which is too recent to apply to the database. 
    An earlier log backup that includes LSN 20000000012200001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Now let us repeat the entire backup process replacing the intermediate backup task with the new option COPY_ONLY.

Create database MyTestDB
go
--At 11PM
Backup database MyTestDB to disk='d:\backup\MyTestDB_FULL.bak' with init
use MyTestDB
go
Create table MyTestTable
(id int, name varchar(100))
go
--Between 11:00 PM and 6:00 AM
insert into MyTestTable select 1,'Claire'
insert into MyTestTable select 2,'Ziyi'
insert into MyTestTable select 3,'Mizuho'
insert into MyTestTable select 4,'Catherine'
insert into MyTestTable select 5,'Jen'
go
--Between 6:00 AM and 7:00 AM
insert into MyTestTable select 11,'Sam'
--At 7:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_1.bak' 
--Between 7:00 AM and 8:00 AM
insert into MyTestTable select 12,'Carrie'
backup log MytestDB to disk='d:\backup\MyTestDB_Log_2.bak' 
--At 8:00AM
--Between 8:00 AM and 9:00 AM
insert into MyTestTable select 13,'Christine'
--At 9:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_3.bak' 
--Between 9:00 AM and 10:00 AM
insert into MyTestTable select 14,'Doug'
--At 10:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_4.bak' 
--Between 10:00 AM and 11:00 AM
insert into MyTestTable select 15,'Jackson'
--At 11:00AM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_5.bak' 
--Between 11:00 AM and 11:29 AM
insert into MyTestTable select 115,'Lewis'
insert into MyTestTable select 119,'Scott'
--At 11:29AM
--Intermediate backup after clint request
BACKUP LOG MYTESTDB TO DISK='C:\MYTESTDB_NEWLOG_BACKUP.BAK' with COPY_ONLY
--Between 11:29 AM and 12:00PM
insert into MyTestTable select 129,'Tiger'
--At 12:00PM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_6.bak' 
--Between 12:00 PM and 1:00 PM
delete from MyTestTable where id =3
--At 1:00PM
backup log MytestDB to disk='d:\backup\MyTestDB_Log_7.bak' 

Let us restore the full backup and all transaction log backups in sequence except the additional transactional log backup we took at 11:29AM. All the restore statements are executed successfully.

Processed 152 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 2 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE DATABASE successfully processed 154 pages in 0.425 seconds (2.951 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 4 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 4 pages in 0.128 seconds (0.240 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.065 seconds (0.007 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.067 seconds (0.007 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.071 seconds (0.007 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.114 seconds (0.004 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.097 seconds (0.015 MB/sec).
Processed 0 pages for database 'MyTestDB', file 'MyTestDB' on file 1.
Processed 1 pages for database 'MyTestDB', file 'MyTestDB_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.084 seconds (0.006 MB/sec).

Conclusion

This article demonstrated how the new feature COPY_ONLY option in the backup command does not affect the archive point, thereby not affecting the restore process. We could do a full backup, transaction log backup and differential backup with the COPY_ONLY option.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM