0

I have a question regarding SQL backups. We have 3 SQK servers (A, B and C). I'm taking a full backup of a DB from server A and restoring it on server B. After that I'm taking a differential backups of the same DB from server A for restoring on server B.

My question is, can I restore the initial full backup of the DB from server A on server C and then, restore the differential backups of the same DB from server B onto server C?

The reason I'm doing this is, we have a storage issue on sServer B, therefore we're not able to take full backups of the DB from server B, so intending to use the full backup from server A in server C and differential backups from server B in server C. Hope this is not confusing.

  • It's not confusing but it doesn't make any sense. Why would you use differential backups from B if you already got a differential from A? – Seth Aug 6 at 5:15
  • Missed that part, Server A is going down, there's only Server B and C we can use, so full backup from A -> B & C and differential backup from B -> C – RKB83 Aug 6 at 5:42
  • But that still doesn't make any sense unless you describe what system they're actually running on. Are they independent? If so why would you need a differential backup? Are they clustered? If so why do you need a differential backup and the synch doesn't happen automatically? – Seth Aug 6 at 7:47
0

To answer your question about mixing backups from different servers during the restore operation I've made a lab like this:

  • SQLA (SQL Server 2014 (SP2-CU12))
  • SQLB (SQL Server 2014 (SP2-CU12))
  • SQLC (SQL Server 2014 (SP2-CU12))

Created a database named TestRestore on full recovery mode and a table tbTest to have some data to check later.
The backup and restore experiment followed like this:

1. Executed on SQLA

Creates the database and sets recovery mode full

CREATE DATABASE TestRestore;
GO
ALTER DATABASE TestRestore SET RECOVERY FULL;
GO

Connects to TestRestore database, creates a table and inserts some data

USE TestRestore;
CREATE TABLE tbTest(ID int);
INSERT INTO tbTest(ID) VALUES(1),(2),(3);
GO

Backup the database (Full backup)

BACKUP DATABASE TestRestore 
    TO DISK = '\\networkBackupShare\TestRestore_SQLA_FULL.bak';
GO

2. Executed on SQLB

RESTORE DATABASE TestRestore 
    FROM DISK = '\\networkBackupShare\TestRestore_SQLA_FULL.bak'
    WITH RECOVERY;
GO

Here I inserted some data to the table on SQLB server before taking the DIFF backup.

USE TestRestore;
INSERT INTO tbTest(ID) VALUES(4),(5),(6);
GO

BACKUP DATABASE TestRestore  
   TO DISK = '\\networkBackupShare\TestRestore_SQLB_DIFF.bak'
   WITH DIFFERENTIAL;  
GO

Finally inserted the last bit of data to the table before taking the LOG backup.

INSERT INTO tbTest(ID) VALUES(7),(8),(9);
GO

BACKUP LOG TestRestore  
   TO DISK = '\\networkBackupShare\TestRestore_SQLB_LOG.bak';
GO

3. Executed on SQLC

Now let's restore our Frankenstein monster and see what happens:

RESTORE DATABASE TestRestore 
    FROM DISK = '\\networkBackupShare\TestRestore_SQLA_FULL.bak'
    WITH NORECOVERY;
GO
RESTORE DATABASE TestRestore 
    FROM DISK = '\\networkBackupShare\TestRestore_SQLB_DIFF.bak'
    WITH NORECOVERY;
GO
RESTORE LOG TestRestore 
    FROM DISK = '\\networkBackupShare\TestRestore_SQLB_LOG.bak' 
    WITH RECOVERY;  
GO

It worked!

Messages from restore commands on SQLC

Conclusion:
Yes, you can mix backup files that came from different servers if the database has the same origin. Beware of two facts though:

  1. The database resulted on server SQLC is a copy of the last state of SQLB, not SQLA.
  2. The experiment only worked because the backup chain hasn't been broken (like taking a FULL BACKUP on SQLB before taking the DIFF and LOG backups), otherwise you'd get a message like this:

Error message from backup restore

  • Thank you taking time to do this, i just wanted to check if somebody had already tried this, i'm gonna try this with the actual full back up, thank you once again – RKB83 Aug 8 at 6:54

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.