Setup a backup file for restore

Joe Chan 04 Aug, 2019 1 Comment

MSSQL-Feature-Image

Parepre of backup for restore:
We create a database named “TESTING”.
We create two tables named “TEST_Table_A”, “TEST_Table_B”
“TEST_Table_A” is within filegroup “Filegroup_A”
“TEST_Table_B” is within filegroup “Filegroup_B”

CREATE DATABASE TESTING;
GO
USE TESTING;
GO
-- Create filegroup named "Filegroup_A" & "Filegroup_B"
ALTER DATABASE TESTING ADD FILEGROUP Filegroup_A;
ALTER DATABASE TESTING ADD FILEGROUP Filegroup_B;
-- Create data file named "Filegroup_A_DF01" and add it to filegroup "Filegroup_A"
ALTER DATABASE TESTING
ADD FILE
(
NAME = Filegroup_A_DF01,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Filegroup_A_DF01.ndf'
)
TO FILEGROUP Filegroup_A;
-- Create data file named "Filegroup_B_DF01" and add it to filegroup "Filegroup_B"
ALTER DATABASE TESTING
ADD FILE
(
NAME = Filegroup_B_DF01,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Filegroup_B_DF01.ndf'
)
TO FILEGROUP Filegroup_B;
-- Create Table_A
CREATE TABLE dbo.TEST_Table_A
(
ID_A int not null IDENTITY(1,1) PRIMARY KEY,
Value_A nvarchar(80) NULL
) on Filegroup_A
-- Create Table_B
CREATE TABLE dbo.TEST_Table_B
(
ID_B int not null IDENTITY(1,1) PRIMARY KEY,
Value_B nvarchar(80) NULL
) on Filegroup_B

We also need to change the recovery mode to FULL.

ALTER DATABASE TESTING SET RECOVERY FULL WITH NO_WAIT;

We insert some data into tables.

-- Insert data to tables
INSERT INTO TEST_Table_A(Value_A) VALUES ('ability'),('able'),('about'),('above'),('accept')
INSERT INTO TEST_Table_B(Value_B) VALUES ('baby'),('back'),('bad'),('bag'),('ball')

Parepre Backup, We will backup as the following order:
1st Full backup
1st Log backup
1st Different backup
2nd Log backup
2nd Different backup
3rd Log backup
2nd Full backup
4th Log backup
3rd Different backup
5th Log backup
4th Different backup
6th Log backup
Tail-log backup

BACKUP DATABASE TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME= '1st Full Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 1st Full backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 1st Full backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME = '1st LOG Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 1st Log backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 1st Log backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP DATABASE TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
DIFFERENTIAL,
MEDIANAME = 'TESTING_BACKUP',
NAME = '1st Differential Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 1st Differential backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 1st Differential backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME = '2nd LOG Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 2nd Log backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 2nd Log backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP DATABASE TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
DIFFERENTIAL,
MEDIANAME = 'TESTING_BACKUP',
NAME = '2nd Differential Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 2nd Differential backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 2nd Differential backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME = '3rd LOG Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 3rd Log backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 3rd Log backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP DATABASE TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME= '2nd Full Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 2nd Full backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 2nd Full backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME = '4th LOG Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 4th Log backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 4th Log backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP DATABASE TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
DIFFERENTIAL,
MEDIANAME = 'TESTING_BACKUP',
NAME = '3rd Differential Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 3rd Differential backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 3rd Differential backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME = '5th LOG Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 5th Log backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 5th Log backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP DATABASE TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
DIFFERENTIAL,
MEDIANAME = 'TESTING_BACKUP',
NAME = '4th Differential Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 4th Differential backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 4th Differential backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MEDIANAME = 'TESTING_BACKUP',
NAME = '6th LOG Backup';
GO
INSERT INTO TEST_Table_A(Value_A) VALUES ('After 6th Log backup')
INSERT INTO TEST_Table_B(Value_B) VALUES ('After 6th Log backup')
GO
WAITFOR DELAY '00:02';
GO
BACKUP LOG TESTING TO
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH CONTINUE_AFTER_ERROR;
GO

How to view the information of the backup:

RESTORE HEADERONLY FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
GO

Author: Joe Chan

1 thought on “Setup a backup file for restore”

Pingback: Restore Database – Joe's choice

Comments are closed.