When to perform a SQL server health check?
A healthy database is essential for staff productivity, customer satisfaction and keeping your company’s efficiency high
FULL Backup Vs COPY ONLY Backup
The backup ensures safety for our data in case of any failure occurs. We should maintain the regular backup strategies.
While taking backup, we should consider the space will occupy by the backup.
Which is the correct time to take a backup (may be non-production hours).
Which backup type, we should choose (Full, Differential, Tlog).
Why we need COPY ONLY BACKUP ?
Consider a below scenario:
Suppose we have design a backup plan for database. Every Sunday we are taking a full backup and daily we are taking differential backup and every hour Tlog backup. So these are automated process.
Now one requirement came to take the full backup, so one of our DBA taken the full backup but we are not aware of that action, days passed our production database got down because of disk failure.
We are in the situation to restore the database from the backup so we will try to restore the complete database but we will get fail because of one full backup that has been taken without our knowledge.
The full backup that has been taken our knowledge will break the LSN chain hence we will face some issues while restoring complete database.
In order to overcome that issue we should use the option copy only while taking the full backup.
Full Backup Without Copy Only:
We will create one database and take the full Backup without the copy only option and using below mentioned TSQL command and note down the LSN.
— Script # 1: Note current 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(‘GeoPITS’)
AND type_desc = ‘ROWS’
GO
Again we will take full Backup and check whether the LSN has been updated or changed.
— Script # 2: Create full backup and compare LSN information
— Create full backup
— Run script after changing Backup path
BACKUP DATABASE AdventureWorks
TO DISK = ‘D:\GeoPITS Backup\GeoPITS.bak’
GO
— Get differential_base_lsn after full backup
SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Updated differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘GeoPITS’)
AND type_desc = ‘ROWS’
GO
— Get LSN of recent full backup for match purpose
SELECT database_name, backup_start_date, is_copy_only,
first_lsn as ‘LSN of full bakup’
FROM msdb..backupset
WHERE database_name = ‘GeoPITS’
ORDER BY backup_start_date DESC
GO
As have seen that the LSN has been changed. It will Break the LSN Chain and throw error while restoring the Differential backup.
Full Backup with copy only:
This time we will backup the same database with copy only option. By using the below mentioned TSQL command. And check whether the LSN has been updated or changed.
— Script # 3: Create full backup with copy only option and compare LSN information
— Create full backup with copy only option
— Run script after changing Backup path
BACKUP DATABASE AdventureWorks
TO DISK = ‘D:\GeoPITS Backup\CopyOnly_GeoPITS.bak’
WITH COPY_ONLY
GO
— Get differential_base_lsn after full backup with copy only option
SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS ‘Un changed differential base LSN’
FROM sys.master_files
WHERE database_id = DB_ID(‘GeoPITS’)
AND type_desc = ‘ROWS’
GO
— Get LSN of recent full backup with copy only option for match purpose
SELECT database_name, backup start_date, is_copy_only,
first_lsn as ‘LSN of last full bakup’
FROM msdb..backupset
WHERE database_name = ‘GeoPITS’
ORDER BY backup_start_date DESC
GO
Now, the LSN has not been changed. Hence LSN chain has not been affected, we can restore the related Differential backup.
