Data Backup and Recovery

SMSQL Copy-Only Backup




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?







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);



Thanks for the reply, though I'm not sure I understand what you have discovered.