RESTORE FULL and DIFFERENTIAL BACKUPS

RESTORE FULL and DIFFERENTIAL BACKUPS

If you need to restore database given following backups files,

• One Full Database Backup File
• Tow Differential Database Backup Files

First you need to have full database backup file as follows,

use masterGOBACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASEUSERGROUP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10;GODECLARE @backupSetId AS intSELECT @backupSetId = position FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP');IF @backupSetId is null BEGIN  RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1); ENDRESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWINDGO

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 113752 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP' on file 1.
Processed 1416 pages for database 'DATABASEUSERGROUP', file 'SALES_01' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_02' on file 1.
Processed 176 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_OLD' on file 1.
Processed 424 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2002' on file 1.
Processed 944 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2003' on file 1.
100 percent processed.
Processed 2 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP_log' on file 1.
BACKUP DATABASE successfully processed 116722 pages in 3.060 seconds (298.002 MB/sec).
The backup set on file 1 is valid.

 Next you need to have two(You need to execute same script twice after change backup file Ex: DIFF_01.bak, DIFF_02.bak)  Differential database backup file as follows, 

use master
GO
BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak'
WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 
NAME = N'DATABASEUSERGROUP-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
DECLARE @backupSetId AS int
SELECT @backupSetId = position FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' and backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' )
IF @backupSetId is null BEGIN
RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1);
END
RESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

29 percent processed.
39 percent processed.
49 percent processed.
59 percent processed.
69 percent processed.
79 percent processed.
89 percent processed.
99 percent processed.
Processed 40 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_01' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_02' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_OLD' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2002' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2003' on file 1.
100 percent processed.
Processed 1 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 81 pages in 0.031 seconds (20.208 MB/sec).
The backup set on file 1 is valid.

After Complete your Backup Task You need to restore above backup file as follows,

USE [master]GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH  FILE = 1,  NORECOVERY,REPLACE, NOUNLOAD,  STATS = 5;GO

You Have to mention NORECOVERY otherwise you can not restore remaining two Differential database backup files.
And After Restore full database backup,

Finally You Need to restore remaining two Differential database backup files as follows,

USE [master]GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_01.bak' WITH  FILE = 1,NORECOVERY, NOUNLOAD,  STATS = 5GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak' WITH  FILE = 1,RECOVERY, NOUNLOAD,  STATS = 5GO

Last Differential database backup files you need to restore as RECOVERY.

Cheers...
and Most welcome your comments and ideas...