24 June,2016 by Jack Vamvas
Question: I’d like to find out whether I can maintain different backup sets in different backup systems. This may sound like a strange question but we are currently transitioning from one backup system platform to another platform. There will be a cross over period .
What are the dangers of proceeding in this practise? Both backup systems use VDI to communicate with SQL Server
Answer: Firstly , be very careful ! Using two backup procedures on the same SQL Server come with a high level of risk
Here are some factors to consider
1) You’ve mentioned both backup systems communicate via VDI to the db servers. The VDI is an API and is installed with SQL server – its purpose is for 3rd party developers to use the BACKUP\RESTORE range provided with sql server .
Ultimately the backup system is running native commands and redirecting the backup copy through to the backup system. The master control is with the Database Engine. This means the rules are set at the Db server via native commands.
2)Maintaining two backup systems may lead to scheduling issues , which backup system would take priority ?
3) The database server would be under load for a longer period of time . Many production systems are time-sensitive for backup run times.
If you had a large ETL job requiring to start at midnight – and all backups need to complete by 23:00 , thi doesn’t allow you a buffer for slow performance or any other reason hindering good progress
4)Breaking log chain . Taking a full backup without COPY_ONLY will break the log chain. So you could have a situation where you’re maintaining FULL BACKUP and transaction logs on one system, you then complete a FULL BACKUP on another system, this will breake the log chain of the first set
5)In a restore situation , which backup system takes priority?
6) Part of the process of transaction log backups is to move part of the file off the system , so we’d have a situation where maybe one part of the transaction log is on one system , the other on the other backup system
Based on these factors it’s not dvisable to proceed with the plan you’re considering .