Danger of maintaining SQL backup copies in different locations

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.

Virtual Device Interface (VDI) and sysadmin (SQL Server DBA)

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

SQL Server - BACKUP LOG WITH NO_LOG (SQL Server DBA)

Copy only backup in SQL server 2005 and not break the backup chain

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 .


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Danger of maintaining SQL backup copies in different locations


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer