--Backup/restore test script for SQL Server (tested with 2012)
 
CREATE DATABASE BackupTest
GO
 
ALTER DATABASE BackupTest
SET RECOVERY FULL WITH NO_WAIT
GO
 
CREATE TABLE BackupTest.dbo.MsgLog (
	ID INT IDENTITY 
		CONSTRAINT PK_MsgLog PRIMARY KEY,
	MsgText VARCHAR(MAX)
)
GO
 
--Simulate some transactions, perform trx log backups...
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before FULL backup')
GO
 
BACKUP DATABASE BackupTest TO DISK = 'BackupTest.Full.bak' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #1')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.01.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #2')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.02.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #3')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.03.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #4')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.04.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #5')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.05.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #6')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.06.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #7')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.07.trn' WITH INIT
GO
--Assume the above backup completed, but RESTORE VERIFY ONLY indicates a bad backup.
 
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before DIFFERENTIAL backup.')
GO
 
--Perform a diff backup to use as a baseline for upcoming trx log backups.
BACKUP DATABASE BackupTest TO DISK = 'BackupTest.Diff.bak' WITH DIFFERENTIAL, INIT
GO
 
--Continue to simulate some transactions, perform trx log backups...
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #8')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.08.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #9')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.09.trn' WITH INIT
GO
 
INSERT INTO BackupTest.dbo.MsgLog (MsgText) VALUES ('Inserted before Trx Log backup #10')
GO
 
BACKUP LOG BackupTest TO DISK = 'BackupTest.10.trn' WITH INIT
GO
 
--Disaster Recovery event takes place.  Time to save the data.
 
--Restore full.
RESTORE DATABASE BackupTest
FROM DISK = 'BackupTest.Full.bak'
WITH REPLACE, NORECOVERY
GO
 
--Restore diff.
RESTORE DATABASE BackupTest
FROM DISK = 'BackupTest.Diff.bak'
WITH NORECOVERY
GO
 
--Skip all trx log backups created prior to the diff,
--Restore all trx log backups created after the diff.
RESTORE DATABASE BackupTest
FROM DISK = 'BackupTest.08.trn'
WITH NORECOVERY
GO
 
RESTORE DATABASE BackupTest
FROM DISK = 'BackupTest.09.trn'
WITH NORECOVERY
GO
 
RESTORE DATABASE BackupTest
FROM DISK = 'BackupTest.10.trn'
WITH NORECOVERY
GO
 
RESTORE DATABASE BackupTest
WITH RECOVERY
GO
 
--Verify no data is lost.
SELECT *
FROM BackupTest.dbo.MsgLog

LS1CYWNrdXAvcmVzdG9yZSB0ZXN0IHNjcmlwdCBmb3IgU1FMIFNlcnZlciAodGVzdGVkIHdpdGggMjAxMikKCkNSRUFURSBEQVRBQkFTRSBCYWNrdXBUZXN0CkdPCgpBTFRFUiBEQVRBQkFTRSBCYWNrdXBUZXN0ClNFVCBSRUNPVkVSWSBGVUxMIFdJVEggTk9fV0FJVApHTwoKQ1JFQVRFIFRBQkxFIEJhY2t1cFRlc3QuZGJvLk1zZ0xvZyAoCglJRCBJTlQgSURFTlRJVFkgCgkJQ09OU1RSQUlOVCBQS19Nc2dMb2cgUFJJTUFSWSBLRVksCglNc2dUZXh0IFZBUkNIQVIoTUFYKQopCkdPCgotLVNpbXVsYXRlIHNvbWUgdHJhbnNhY3Rpb25zLCBwZXJmb3JtIHRyeCBsb2cgYmFja3Vwcy4uLgpJTlNFUlQgSU5UTyBCYWNrdXBUZXN0LmRiby5Nc2dMb2cgKE1zZ1RleHQpIFZBTFVFUyAoJ0luc2VydGVkIGJlZm9yZSBGVUxMIGJhY2t1cCcpCkdPCgpCQUNLVVAgREFUQUJBU0UgQmFja3VwVGVzdCBUTyBESVNLID0gJ0JhY2t1cFRlc3QuRnVsbC5iYWsnIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzEnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wMS50cm4nIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzInKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wMi50cm4nIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzMnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wMy50cm4nIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzQnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wNC50cm4nIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzUnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wNS50cm4nIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzYnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wNi50cm4nIFdJVEggSU5JVApHTwoKSU5TRVJUIElOVE8gQmFja3VwVGVzdC5kYm8uTXNnTG9nIChNc2dUZXh0KSBWQUxVRVMgKCdJbnNlcnRlZCBiZWZvcmUgVHJ4IExvZyBiYWNrdXAgIzcnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4wNy50cm4nIFdJVEggSU5JVApHTwotLUFzc3VtZSB0aGUgYWJvdmUgYmFja3VwIGNvbXBsZXRlZCwgYnV0IFJFU1RPUkUgVkVSSUZZIE9OTFkgaW5kaWNhdGVzIGEgYmFkIGJhY2t1cC4KCgpJTlNFUlQgSU5UTyBCYWNrdXBUZXN0LmRiby5Nc2dMb2cgKE1zZ1RleHQpIFZBTFVFUyAoJ0luc2VydGVkIGJlZm9yZSBESUZGRVJFTlRJQUwgYmFja3VwLicpCkdPCgotLVBlcmZvcm0gYSBkaWZmIGJhY2t1cCB0byB1c2UgYXMgYSBiYXNlbGluZSBmb3IgdXBjb21pbmcgdHJ4IGxvZyBiYWNrdXBzLgpCQUNLVVAgREFUQUJBU0UgQmFja3VwVGVzdCBUTyBESVNLID0gJ0JhY2t1cFRlc3QuRGlmZi5iYWsnIFdJVEggRElGRkVSRU5USUFMLCBJTklUCkdPCgotLUNvbnRpbnVlIHRvIHNpbXVsYXRlIHNvbWUgdHJhbnNhY3Rpb25zLCBwZXJmb3JtIHRyeCBsb2cgYmFja3Vwcy4uLgpJTlNFUlQgSU5UTyBCYWNrdXBUZXN0LmRiby5Nc2dMb2cgKE1zZ1RleHQpIFZBTFVFUyAoJ0luc2VydGVkIGJlZm9yZSBUcnggTG9nIGJhY2t1cCAjOCcpCkdPCgpCQUNLVVAgTE9HIEJhY2t1cFRlc3QgVE8gRElTSyA9ICdCYWNrdXBUZXN0LjA4LnRybicgV0lUSCBJTklUCkdPCgpJTlNFUlQgSU5UTyBCYWNrdXBUZXN0LmRiby5Nc2dMb2cgKE1zZ1RleHQpIFZBTFVFUyAoJ0luc2VydGVkIGJlZm9yZSBUcnggTG9nIGJhY2t1cCAjOScpCkdPCgpCQUNLVVAgTE9HIEJhY2t1cFRlc3QgVE8gRElTSyA9ICdCYWNrdXBUZXN0LjA5LnRybicgV0lUSCBJTklUCkdPCgpJTlNFUlQgSU5UTyBCYWNrdXBUZXN0LmRiby5Nc2dMb2cgKE1zZ1RleHQpIFZBTFVFUyAoJ0luc2VydGVkIGJlZm9yZSBUcnggTG9nIGJhY2t1cCAjMTAnKQpHTwoKQkFDS1VQIExPRyBCYWNrdXBUZXN0IFRPIERJU0sgPSAnQmFja3VwVGVzdC4xMC50cm4nIFdJVEggSU5JVApHTwoKLS1EaXNhc3RlciBSZWNvdmVyeSBldmVudCB0YWtlcyBwbGFjZS4gIFRpbWUgdG8gc2F2ZSB0aGUgZGF0YS4KCi0tUmVzdG9yZSBmdWxsLgpSRVNUT1JFIERBVEFCQVNFIEJhY2t1cFRlc3QKRlJPTSBESVNLID0gJ0JhY2t1cFRlc3QuRnVsbC5iYWsnCldJVEggUkVQTEFDRSwgTk9SRUNPVkVSWQpHTwoKLS1SZXN0b3JlIGRpZmYuClJFU1RPUkUgREFUQUJBU0UgQmFja3VwVGVzdApGUk9NIERJU0sgPSAnQmFja3VwVGVzdC5EaWZmLmJhaycKV0lUSCBOT1JFQ09WRVJZCkdPCgotLVNraXAgYWxsIHRyeCBsb2cgYmFja3VwcyBjcmVhdGVkIHByaW9yIHRvIHRoZSBkaWZmLAotLVJlc3RvcmUgYWxsIHRyeCBsb2cgYmFja3VwcyBjcmVhdGVkIGFmdGVyIHRoZSBkaWZmLgpSRVNUT1JFIERBVEFCQVNFIEJhY2t1cFRlc3QKRlJPTSBESVNLID0gJ0JhY2t1cFRlc3QuMDgudHJuJwpXSVRIIE5PUkVDT1ZFUlkKR08KClJFU1RPUkUgREFUQUJBU0UgQmFja3VwVGVzdApGUk9NIERJU0sgPSAnQmFja3VwVGVzdC4wOS50cm4nCldJVEggTk9SRUNPVkVSWQpHTwoKUkVTVE9SRSBEQVRBQkFTRSBCYWNrdXBUZXN0CkZST00gRElTSyA9ICdCYWNrdXBUZXN0LjEwLnRybicKV0lUSCBOT1JFQ09WRVJZCkdPCgpSRVNUT1JFIERBVEFCQVNFIEJhY2t1cFRlc3QKV0lUSCBSRUNPVkVSWQpHTwoKLS1WZXJpZnkgbm8gZGF0YSBpcyBsb3N0LgpTRUxFQ1QgKgpGUk9NIEJhY2t1cFRlc3QuZGJvLk1zZ0xvZw==