RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.
Examples (Transact-SQL)
A. Restoring a differential database backup
This example restores a database and differential database backup of the MyAdvWorks database.
-- Assume the database is lost, and restore full database,
-- specifying the original full database backup and NORECOVERY,
-- which allows subsequent restore operations to proceed.
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY;
GO
-- Now restore the differential database backup, the second backup on
-- the MyAdvWorks_1 backup device.
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH FILE = 2,
RECOVERY;
GO
B. Restoring a database, differential database, and transaction log backup
This example restores a database, differential database, and transaction log backup of the MyAdvWorks database.
-- Assume the database is lost at this point. Now restore the full
-- database. Specify the original full database backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed.
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY;
GO
-- Now restore the differential database backup, the second backup on
-- the MyAdvWorks_1 backup device.
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH FILE = 2,
NORECOVERY;
GO
-- Now restore each transaction log backup created after
-- the differential database backup.
RESTORE LOG MyAdvWorks
FROM MyAdvWorks_log1
WITH NORECOVERY;
GO
RESTORE LOG MyAdvWorks
FROM MyAdvWorks_log2
WITH RECOVERY;
GO Example
-- View the data and log files in the backup files
RESTORE FILELISTONLY
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_01.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_02.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_03.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_04.bak'
---
USE master
GO
---------------
-- FULL
/*
RESTORE DATABASE TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_01.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_02.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_03.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_04.bak'
WITH REPLACE, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
*/
---------------
-- FULL + DIFF
-- FULL (NORECOVERY)
RESTORE DATABASE TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_01.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_02.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_03.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_04.bak'
WITH NORECOVERY, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
-- DIFF (RECOVERY)
RESTORE DATABASE TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_01.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_02.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_03.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_04.bak'
WITH RECOVERY, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
---------------
-- FULL + DIFF + LOG
-- FULL (NORECOVERY)
RESTORE DATABASE TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_01.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_02.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_03.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Full_04.bak'
WITH NORECOVERY, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
-- DIFF (NORECOVERY)
RESTORE DATABASE TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_01.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_02.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_03.bak',
DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Diff_04.bak'
WITH NORECOVERY, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
-- LOG 1 (NORECOVERY)
RESTORE LOG TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Log_01.trn'
WITH NORECOVERY, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
-- LOG 2 (RECOVERY)
RESTORE LOG TestRestoreDB
FROM DISK = 'D:\SQLServerBackups\TestRestoreBackup\TestRestoreDB_Log_02.trn'
WITH RECOVERY, maxtransfersize = 4194302, buffercount = 96, stats = 5
GO
---------------
USE TestRestoreDB
GO
SELECT * FROM dbo.tbTest
/*
Col01 Col02
1 One
2 Two
3 Three
4 Full Backup Completed
5 Five
6 Six
7 Diff Backup Completed
8 Eight
9 Nine
10 Log 1 Backup Completed
11 Eleven
12 Tweleve
13 Log 2 Backup Completed
14 Fourteen
15 Fifteen
*/
Comments