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
  5. …

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 
-------------------------------- 
USE MASTER

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
---------------------------	
DECLARE @intLoop AS INTEGER
DECLARE @strText AS VARCHAR(50)

SET @intLoop = 10

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

		---------------------------	
		--	Begin Transaction
		---------------------------	
		BEGIN TRANSACTION

			---------------------------	
			--	Insert into Table1
			---------------------------	
			BEGIN TRY
				INSERT INTO Base1.dbo.Table1
				VALUES
				(
				@strText 
				)
			END TRY

			---------------------------	
			--	Rollback Transaction
			--	If insertion fails
			---------------------------				
			BEGIN CATCH
			    IF @@TRANCOUNT > 0
				ROLLBACK TRANSACTION;
			END CATCH

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

		---------------------------	
		--	Commit Transaction
		---------------------------			
		IF @@TRANCOUNT > 0
		COMMIT TRANSACTION;

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

	END

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!