2017-07-07 04:00 AM
I ned to run a SMSQL copy-only backup. I want a full backup, a backup of the logs but with no log truncation. The SMSQL wizard is confuring:
1. Select whether to perform a database backup or verification operation: select to 'Back up database and transaction log'
2. Backup Type: 'Full' and select 'copy-only backup' - what does the copy-only option do exactly?
3. Now this confuses me 'Select whether to automatically back up the transaction loag after the full database backup, so that portions of the transaction logs that contain committed transaction will be truncated and recycled for logging future transactions' - the options are 'yes' or 'no'
I do want to run a trnsaction log backup after the full database however I don't want to truncate the logs - so shich option do i select?
4. Backup Setting>Transaction Log Backup. There's an option here to 'Truncate committed transaction in the transaction log' - it seems that if this is selected the wizard summary at the end suggests the logs will be truncated, even though 'copy-only' has been selected in an earlier screen
If the option is left unselected then the summary wizard states that the logs won't be truncated
As an aside, are the Backup Settings only applicable when using the wizard (once only), or are they referred to during a backup operation?
2017-07-11 11:45 PM - edited 2017-07-11 11:51 PM
I confirmed thier behaviors "Copy-Only backup" and "Truncate committed transactions in the transaction log".
Transaction log remains only check "Truncate committed transactions in the transaction log".
"Copy-Only backup" delete transaction logs after backup.
So I don't know what is the behavior of "Copy-Only backup".
I confirmed using SQL query.
SELECT * FROM sys.fn_dblog(NULL,NULL);