All about SQLServer: SqlServer

Database – Restoration process:

While restoring database , the differential database backups will have a reference to the full database backup , i.e through LSN (Log sequence number) .

If a full database backup is taken , any subsequent differential database backups
use this latest full database backup as their base .

Hence ,database is restored with the help of sequence of LSN .

LSN 's are stored with the backup file is to verify that the backup files are being restored in the proper sequence .

Copy-Only Backup:

A Copy-only backups will not break the restore chain,which means the differential database backups will still have a reference to the last full database backup taken , it will not consider the intermediate full database backup taken with copy-only option .

Copy-only backup can be taken , when you want to get a copy of the database for testing purposes .

In SSMS 2005,Copy-only backups are not supported via SQL Server Management Studio (SSMS) and must be performed via the Transact-SQL (T-SQL) BACKUP command.

In SSMS 2008 ,it supports GUI or T-SQL statement to create Copy Only backups.

Example:

To understand copy-only backup , take full back with & without copy-only option and observe the change by executing the below queries each time after taking backup.

Query 1:

SELECT DB_NAME(database_id) AS [DB Name],

differential_base_lsn AS 'differential backup LSN - referencing full backup'

FROM sys.master_files

WHERE database_id = DB_ID('AdventureWorks')

AND type_desc = 'ROWS'

Query 2:

SELECTdatabase_name, backup_start_date, is_copy_only,

first_lsn as 'LSN of full backup'

FROM msdb..backupset

WHERE database_name = 'AdventureWorks'

ORDER BY backup_start_date DESC

GO

Backup with Copy-only option:

'differential backup LSN - referencing full backup’ from query 1 will match with 'LSN of full backup' from query 2 , if you take full back up with copy only option .Though you will observe LSN of full backup taken with copy-only option as latest entry , it can be ignored by considering is_copy_only = 1

Backup without Copy-only option:

If you take full back up without copy only option, ‘LSN of full backup' from query 2 will have new entry of latest full backup taken and ' differential backup LSN - referencing full backup’ from query 1 will get updated accordingly , referencing the LSN of latest full backup taken .

To take copy-only backup,check "copy-only backup" as shown in below image: