We wish to perform backup in SQL Server in 2 different ways in parallel. Now currently we have a 3rd party tool that is performing the Backups, Full and transaction log, now the business wants to perform also backups on disk via local SQL Server jobs. The question is that would be possible, the LSNs chain will be affected as I know, and it won't be possible to perform valid restores, is that correct? Even If we will perform Full backup and Transactional backup on backup server and also full backup and transaction backup to be keep on local disk via SQL jobs. Would it be possible?

  • What is the version of sql server? – KASQLDBA Jun 22 '15 at 10:11
  • As a way to placate the business, you could create COPY_ONLY full backups via the agent, until you can address whatever weaknesses in your current backup solution prompted the perceived need for a backup backup solution! – Avarkx Jun 22 '15 at 10:15
  • it is from SQL 2005 to SQL 2012, there are multiple servers. – Andy Jun 22 '15 at 10:54
  • Thing is, if we already perform backup with a backup tool then already there is a backup LSN chain and if I will also set to be performed on disk via SQL jobs, then the LSN it wouldn't be valid anymore, correct? – Andy Jun 22 '15 at 10:56

There is no way to take additional transaction log backups without affecting the LSN chain. Even taking COPY_ONLY transaction log backups would be completely pointless, as the log chain of the COPY_ONLY backups would be incomplete. You can read more on that here.

You could take additional FULL backups to a local disk without affecting the log chain (if you don't take DIFF backups, the COPY_ONLY flag is not needed, as it only affects the differential base).

If the third-party tool is unreliable in some way, you could back up to a local or network disk and have the backup tool take copies of the backup files instead of taking database backups.

However, if you have issues with that product, I would rather focus on fixing those issues. Many backup tools support a limited subset of the SQL Server backup options and try to treat databases as "files" to better fit in the general purpose backup model they already have. If this is the kind of issues you're hitting, you could also consider dropping the tool altogether and use it to backup your backup files. The main advantage of such tools is centralized management and scheduling for all backups across all instances, which is something you would lose if you switch to SQL Server Agent controlled schedules.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.