September 26, 2018 at 8:42 am
I need to take a adhoc full backup on a prod server. Can I take the backup with out copyonly option with out interrupt the lan of the backup chain.
September 26, 2018 at 8:45 am
Copy-only backups only really matter if you also take differential backups. If you don't, it won't make a difference. Full backups don't "break the log chain", they only reset the differential bitmap.
https://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
September 26, 2018 at 9:15 am
Just remember that you might not be the only person taking backups.
eg Your systems team might be taking VSS backups which could be differential as well as full.
You can check with something like:
SELECT TOP (1) S.backup_start_date, S.backup_finish_date, M.physical_device_name
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
WHERE S.[type] = 'I'
AND S.database_name = '<Database_Name, sysname, Database_Name>'
-- 7 is the snapshot done by tape backup
AND M.device_type = 7 --2
ORDER BY S.backup_finish_date DESC;
September 26, 2018 at 12:15 pm
Can I ask why Copyonly is something you don't want to do?
September 27, 2018 at 10:03 am
I heard in later versions with out copyonly option we can take full backup with out interrupting the lsn .is it true?
September 27, 2018 at 10:19 am
backup has never interrupted the log chain. Only the differential chain is impacted with a full backup. Copy only is for avoiding differential issues.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply