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.
Regards
Prince Rastogi
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
