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?
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