Backup feature – Copy_only


All ad-hoc backup’s requests should be treated important as this can harm your recovery plan during emergency.Starting SQL 2005 we have an option to create COPY_ONLY backup which will not alter the differential_log_sequence(The differential_base_lsn affects the sequence in which combination of backups are to be restored in a recovery).

This concept is proved and tested using the below scripts.

–Backup the database AdventureWorksDW

BACKUP DATABASE AdventureWorksDW
TO DISK =’C:\Backup\AdventureWorksDW_Full.bak’

–Check the differential_base_lsn

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Note differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘AdventureWorksDW’)
AND type_desc = ‘ROWS’
GO

/*LSN found was 31000000011300037*/

–Prove that normal full backup will change the differential_base_lsn

BACKUP DATABASE AdventureWorksDW
TO DISK =’C:\Backup\AdventureWorksDW_Full.bak’

GO

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Note differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘AdventureWorksDW’)
AND type_desc = ‘ROWS’
GO

/*LSN found was 31000000013800037 */
–Now take a Copy-Only backup and we can note that LSN is not changed.

BACKUP DATABASE AdventureWorksDW
TO DISK =’C:\Backup\AdventureWorksDW_Full.bak’
WITH COPY_ONLY

GO

SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Note differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘AdventureWorksDW’)
AND type_desc = ‘ROWS’
GO

/*LSN found was 31000000013800037 */

Advertisements

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