Restore Database

We will use the backup files which set up at “Setup a backup file for restore“.
Restore Backup file in the following order:
1. The most recent “Full Backup”
2. The most recent “Differential Backup”
3. All “Log Backup” in chronological order

“WITH NORECOVERY”
1. All restore except the last one must contain this.
2. Roll back will not occur.
3. Roll forward allows to continue.

“WITH RECOVERY”
1. The last restore should contain this.
2. Roll back will occur.
3. Roll forward is completed.

“WITH REPLACE”
1. If we does not take a tail log backup before the restore, the error will raised.
2. We can use “WITH REPLACE” or take a tail log to solve this error.

Restore from a full backup:
We will restore DB to 2nd Full backup

-- Using the following SQL to find out 2nd Full Backup's position
-- We find that 2nd Full Backup's position is 7.
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

-- The restore DB cannot be in use.
-- If not, error will be raised.
-- We need to change the current DB to other DB
-- Since we does not take a tail log backup,
-- We need to use "REPLACE" or take a tail log backup.
RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 7,
REPLACE;

Restore from a different backup:
We will restore DB to 2nd differential backup

-- Using the following SQL to find out 2nd Full Backup's position
-- We find that 1nd Full Backup's position is 1.
-- We find that 2nd Differential Backup's position is 5.
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

-- The restore DB cannot be in use.
-- If not, error will be raised.
-- We need to change the current DB to other DB
-- We restore to 1st Full Backup
-- We need "REPLACE" and "NORECOVERY"
RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 1,
REPLACE,
NORECOVERY;
-- Now we retore to 2nd Differential Backup
RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 5,
RECOVERY;

Restore from a log backup:
We will restore DB to 5th log backup

-- Using the following SQL to find out 2nd Full Backup's position
-- We find that the recent Full Backup's position is 7.
-- We find that the recent Differential Backup's position is 9.
-- We find that 5th Log Backup's position is 10.
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

-- The restore DB cannot be in use.
-- If not, error will be raised.
-- We need to change the current DB to other DB
-- We restore to the recent Full Backup
-- We need "REPLACE" and "NORECOVERY"
RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 7,
REPLACE,
NORECOVERY;
-- Now we retore to the recent Differential Backup
RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 9,
NORECOVERY;
-- Now we restore to 5th Log Backup
RESTORE LOG TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 10,
RECOVERY;

Restore to special point of time:
We will restore to ‘2019-08-11 17:33:38’

-- Using the following SQL to find out '2019-08-11 17:33:38'
-- We find that the recent Backup's position is 6.
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

RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 1,
STOPAT = '2019-08-11 17:33:38',
REPLACE,
NORECOVERY;

RESTORE LOG TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 2,
STOPAT = '2019-08-11 17:33:38',
NORECOVERY;

RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 3,
STOPAT = '2019-08-11 17:33:38',
NORECOVERY;

RESTORE LOG TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 4,
STOPAT = '2019-08-11 17:33:38',
NORECOVERY;

RESTORE DATABASE TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 5,
STOPAT = '2019-08-11 17:33:38',
NORECOVERY;

RESTORE LOG TESTING FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
File = 6,
STOPAT = '2019-08-11 17:33:38',
RECOVERY;

Restore from backup to other Database:
We will restore 4th Log backup to DB named “HELLO”

-- Using the following SQL to find out the LogicalName of the files.
-- We need to move those files to other location
RESTORE FILELISTONLY FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
GO

-- Using the following SQL to find out 2nd Full Backup's position
-- We find that the recent Full Backup's position is 7.
-- We find that 4th Log Backup's position is 8.
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

-- The restore DB cannot be in use.
-- If not, error will be raised.
-- We need to change the current DB to other DB
USE Master
-- We restore to the recent Full Backup
-- We need "REPLACE" and "NORECOVERY"
-- We move files to C:\Hello
RESTORE DATABASE HELLO FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MOVE 'TESTING' TO 'C:\Hello\TESTING.mdf',
MOVE 'Filegroup_A_DF01' TO 'C:\Hello\Filegroup_A_DF01.ndf',
MOVE 'Filegroup_B_DF01' TO 'C:\Hello\Filegroup_B_DF01.ndf',
MOVE 'TESTING_log' TO 'C:\Hello\TESTING_log.ldf',
File = 7,
REPLACE,
NORECOVERY;
-- Now we restore to 5th Log Backup
RESTORE LOG HELLO FROM
DISK='\\WS01\Sources\TESTING_BACKUP_01.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_02.bak',
DISK='\\WS01\Sources\TESTING_BACKUP_03.bak'
WITH
MOVE 'TESTING' TO 'C:\Hello\TESTING.mdf',
MOVE 'Filegroup_A_DF01' TO 'C:\Hello\Filegroup_A_DF01.ndf',
MOVE 'Filegroup_B_DF01' TO 'C:\Hello\Filegroup_B_DF01.ndf',
MOVE 'TESTING_log' TO 'C:\Hello\TESTING_log.ldf',
File = 8,
RECOVERY;

Joe's choice

Restore Database

Author: Joe Chan

Search Me

Recent Posts

Author: Joe Chan

Search Me

Recent Posts

Tag Cloud