FAQs: Database backup and restore for a mirrored database

Recently, I was asked several regarding database backup and restore for a mirrored database; therefore I think I need to write a blog post about them which may help others who has the similar doubts.

Q: Can I do database backups for a mirrored database?

A: Yes, it is allowed to do database backups in the Principal database. However, it is not allowed on the Mirror server since which is in the Restoring state.

Q: Can I do transactional log or differential database backups on mirror database after mirroring session fails over?

A: Yes. Someone may think full database can be performed on the mirror database, but not think so for transactional log (t-log) or differential database backups. Actually, we can t-log or differential database backups as well as long as there was already one full database backed up before in either server and both database has been synced up once after that..

Q: Can I restore the database with backup files which are backed up from differential server?

A: Yes, but these backup file must be in a valid Recovery Path. For more information, see: Working with Restore Sequences for SQL Server Databases.

Demo:

Make sure you have already setup a mirroring environment before following the demo steps below. If not, you can set it up following a previous blog post: https://ifcsong.wordpress.com/2011/10/11/setup-database-mirroring-using-windows-authentication-t-sql/.

:SETVAR principal_server "PRINCIPAL"
:SETVAR mirror_server "MIRROR"
:SETVAR restore_test "TEST"

-- Step 1: Perform some changes on the principal server
:CONNECT $(principal_server)
USE DBMirroringTest;
GO
CREATE TABLE dbo.backuptest
(
	Id int not null identity(1,1) primary key,
	Operation nvarchar(100)
);
GO
INSERT INTO dbo.backuptest(Operation) VALUES ('1st row inserted on the principal server');
GO

-- Step 2: Perform the 1st full database backup on the principal server
:CONNECT $(principal_server)
USE master;
GO
BACKUP DATABASE DBMirroringTest
TO DISK = 'E:\Workspace\Backups\1stfullbackup.bak'
WITH INIT, FORMAT, STATS = 10;
GO

-- Step 3: Insert some more records in the table on the principal server
:CONNECT $(principal_server)
USE DBMirroringTest
INSERT INTO dbo.backuptest(Operation) VALUES ('2st row inserted on the principal server after full backup');
GO

-- Step 4: Perform the 1st log backup on the principal server
:CONNECT $(principal_server)
USE master;
GO
BACKUP LOG DBMirroringTest
TO DISK = 'E:\Workspace\Backups\1stlogbackup.trn'
WITH INIT, FORMAT, STATS = 10;
GO

-- Step 5: Failover the database mirroring from principal server
:CONNECT $(principal_server)
USE master;
GO
ALTER DATABASE DBMirroringTest SET PARTNER FAILOVER;
GO

-- Step 6: Insert some more records in the table on the new principal (oringal mirror) server
:CONNECT $(mirror_server)
USE DBMirroringTest;
GO
INSERT INTO dbo.backuptest(Operation) VALUES ('3st row inserted on the new principal (original mirror) server after 1st log backup');
GO

-- Step 7: Perform the 1st differential backup on the new principal server (original mirror)
:CONNECT $(mirror_server)
USE master;
GO
BACKUP DATABASE DBMirroringTest
TO DISK = 'E:\Workspace\Backups\1stdiffbackup.bak'
WITH DIFFERENTIAL, INIT, FORMAT, STATS = 10;
GO


-- Step 8: Perform the 2nd log backup on the new principal server (original mirror)
:CONNECT $(mirror_server)
USE master;
GO
BACKUP LOG DBMirroringTest
TO DISK = 'E:\Workspace\Backups\2stlogbackup.trn'
WITH INIT, FORMAT, STATS = 10;
GO


:CONNECT $(restore_test)
-- Test 1:
-- Restore the 1st full backup and 1st log backup from the principal server and 2nd log backup
-- from the new principal server (original mirror)
USE master;
GO
RESTORE DATABASE DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stfullbackup.bak'
WITH 
	MOVE 'DBMirroringTest'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_data.mdf',
	MOVE 'DBMirroringTest_log'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_log.ldf',
REPLACE, NORECOVERY;
GO
RESTORE LOG DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stlogbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE LOG DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\2stlogbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE DATABASE DBMirroringTest WITH RECOVERY;
GO

-- Check the result
USE DBMirroringTest;
GO 
SELECT * FROM dbo.backuptest;
GO

-- Drop the database
USE master;
GO
DROP DATABASE DBMirroringTest;
GO

-- Test 2:
-- Restore the 1st full backup and 1st differential backup  and 2nd log backup
-- from the new principal server (original mirror)
USE master;
GO
RESTORE DATABASE DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stfullbackup.bak'
WITH 
	MOVE 'DBMirroringTest'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_data.mdf',
	MOVE 'DBMirroringTest_log'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_log.ldf',
REPLACE, NORECOVERY;
GO
RESTORE DATABASE DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stdiffbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE LOG DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\2stlogbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE DATABASE DBMirroringTest WITH RECOVERY;
GO

-- Check the result
USE DBMirroringTest;
GO 
SELECT * FROM dbo.backuptest;
GO

-- Drop the database
USE master;
GO
DROP DATABASE DBMirroringTest;
GO
Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Backup, Database Mirroring, Restore and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s