POC about crossed backups

I recently had to check crossed backup made with two different technologies:

  1. HP 3PAR
  2. Netbackup

I noticed by digging in the SQL Server backupset, that a Full backup was made by HP 3PAR just between two translog backups performed by Netbackup. In other words I had the following chronology:

  1. Backup Full Netbackup
  2. Backup Log Netbackup
  3. Backup Full HP 3PAR
  4. Backup Log Netbackup

I wanted to be sure being able to restore the Netbackup logs in order to respect the SLA, just in case.

So I decided to build a Proof Of Concept in order to validate that one technology is not incompatible with an other one.

Building the POC

I created two databases;

  • Base1
  • Base2

within an SQL Server instance

In each Database, I created one table, with one field, in order to insert data like ‘Transaction1’, ‘Transaction2’, and so on…

I aimed to be able to follow exactly each inserted row and being able to restore each database at a certain point. In this way I easily can give a conclusion

The goal of this POC was to infirm or confirm the following hypothesis:

  • You can restore a Full + Logs Netbackup backups even if a Full HP 3 PAR is performed between the 2 Netbackup logs.
  • There is no lost transactions due to instance stop or anything else in the Base2 while I’m restoring the Base1 with a HP 3PAR technology

To validate the second hypothesis, I decide to write a script inserting numbered transactions in the Base2 every 5 seconds, while I’ m restoring the other database.

Insertion script

Here is the script with the different steps, to be executed one after the other.

After each step I performed a backup as indicated in the comments:

---- Init 

INSERT INTO Base1.dbo.Table1(Transactiontext) VALUES ( 'Transaction1' )

INSERT INTO Base2.dbo.Table1 VALUES ( 'Transaction1' )

---- Backup FULL via Netbackup 

INSERT INTO Base1.dbo.Table1 VALUES ( 'Transaction2' )

INSERT INTO Base2.dbo.Table1 VALUES ( 'Transaction2' ) 

-- Backup LOG via Netbackup 

INSERT INTO Base1.dbo.Table1 VALUES ( 'Transaction3' )

INSERT INTO Base2.dbo.Table1 VALUES ( 'Transaction3' )

-- Backup FULL via 3PAR 

INSERT INTO Base1.dbo.Table1 VALUES ( 'Transaction4' )

INSERT INTO Base2.dbo.Table1 VALUES ( 'Transaction4' )

---- Backup LOG via Netbackup 

Conclusion about restorations mixing two technologies

For my part, I restored the databases at different step, to make sure everything was okay and with the right data, by querying the table.

I ‘ve been able to restore a Netbackup

  1. Full backup
  2. Log backup
  3. Log Backup

with a Full backup between the two backups log.

In conclusion, the HP3PAR backup technology is compatible with the netbackup technology.

Restoring with HP 3PAR technology

I heard about an impossibility to restore a database without stopping the SQL Server instance. The technical guy I had on the phone about this subjet seemed to be clear about that, and it appeared to mee like a real risk, because this technology is used on clustered that often share several instances with several databases inside each one.

I decided to try to restore a full backup on the base1 while i was inserting data in the Base2 every 5 seconds. Here is the script:

USE master 

--	Variable Declaration

SET @intLoop = 10

--	Loop and insert value
--	every 5 seconds
		--	Build value to insert
		SET @strText = 'Transation' + CONVERT(VARCHAR(10),@intLoop)

		--	Begin Transaction

			--	Insert into Table1
				INSERT INTO Base1.dbo.Table1

			--	Rollback Transaction
			--	If insertion fails
			    IF @@TRANCOUNT > 0

		--	Increment @IntLoop
		SET @intLoop = @intLoop + 1

		--	Commit Transaction

		--	Delay 5 seconds
		WAITFOR DELAY '00:00:05'


Conclusion about the 3PAR technology

By defining a virtual copy on each database with the 3PAR interface, we have been able to restore a database while inserting data every 5 seconds on the other without any loss of transactions.

There is no stop of instance, the process is independant with a good granularity.

Conclusion about this article

Never trust people who do not give you clear and stamped conclusions, with POC and things you can check and easily replay. A lot of people selling software don’t even know their products and can lead you to misunderstanding and bad knowledge.

Do not hesitate to test by yourself and I hope this short article will give you a base to do that!