Copy Only Backup in SQL Server 2005


Most of the times, customers ask to refersh their development or staging database with a production copy. However, it is not recomended to create a full backup of the production DBs on demand as it breaks the differential backup chain, if you are not going to save the full backup copy for future use. With SQL Server 2005, we can still create a full backup without breaking the differential backup chain, with the COPY_ONLY clause in t-sql. Through SSMS GUI, it is not possible to create a copy-only backup.
 
A copy-only backup is a one time snapshot of the database. Copy Only Backup is supported on all recovery model however, a following differential backup is not possible. With this, your existing backup strategy will not be affected as it doesn’t make any changes to the LSN. The syntax for copy only backup of SKG_TEST database is mentioned below:
 

— Copy Only Backup of SKG_TEST

BACKUPDATABASE [SKG_TEST] TODISK  = N‘C:\Backup\Test\SKG_TEST_Copy.bak’

WITHCOPY_ONLY

GO

I normally prefer to use the above script to create a database backup, when my customers requested to refersh their dev/stag environment with a most recent copy of their production instance. By doing this, it didn’t break the current differential backup chain, while still complete the requested task. To know more about copy only backup in SQL Server 2005, visit the following link: http://technet.microsoft.com/en-us/library/ms191495(SQL.90).aspx

Let me know, whats your take on this?

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

One Response to Copy Only Backup in SQL Server 2005

  1. Donald says:

    Hi Sudeepta,
    With copy only backup, can I restore a copy only backup in “recovery” mode so that I can roll forward all new trans log after the copy-only backup?
    Tks,
    Donald

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s