Verifying and Restoring a Differential Backup using T-SQL In SQL Server

In the previous post, we saw how to perform a Full and Differential Backup using T-SQL. In this post, let us quickly see how to restore a differential database using the backup.

Verifying BackUp

The first step is to verify the backup. Use the RESTORE command to verify the contents of the backup. There are certain arguments that can be used with the RESTORE command. The RESTORE FILELISTONLY lists the database and log files contained in the backup set.

RESTORE FILELISTONLY

FROM DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

 

Output:

LogicalName    PhysicalName    Type    FileGroupName    Size    MaxSize    FileId    CreateLSN    DropLSN    UniqueId    ReadOnlyLSN    ReadWriteLSN    BackupSizeInBytes    SourceBlockSize    FileGroupId    LogGroupGUID    DifferentialBaseLSN    DifferentialBaseGUID    IsReadOnly    IsPresent    TDEThumbprint

Northwind    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\northwnd.mdf    D    PRIMARY    4456448    35184372080640    1    0    0    FEF51490-23C0-4472-A18D-E6E95B9FBF0E    0    0    393216    512    1    NULL    41000000033100113    F0FCC4D0-4C2A-40C3-941F-5934CA5E2939    0    1    NULL

Northwind_log    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\northwnd.ldf    L    NULL    1048576    2199023255552    2    0    0    37CE9E1A-E2ED-4706-8D08-3BE2C28EFF1A    0    0    0    512    0    NULL    0    00000000-0000-0000-0000-000000000000    0    1    NULL

I also find the RESTORE VERIFYONLY very useful to check if the backup set is complete and the entire backup is readable.

 

RESTORE VERIFYONLY

FROM DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

WITH FILE = 1,

LOADHISTORY

 

Output:

The backup set on file 1 is valid.

You can read more about the other RESTORE Arguments over here.

Restoring Backup

Once we have verified the back up, we can now go ahead and restore the Full and Differential Backup using the following T-SQL

-- Restore the Full BackUp

RESTORE DATABASE Northwind

FROM DISK = 'C:\MyBackUp\NW_Mar20_09.bak'

WITH NORECOVERY, REPLACE

 

 

-- Now Restore the differential database backup

RESTORE DATABASE Northwind

   FROM DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

   WITH FILE = 2,

   RECOVERY

GO

In a similar manner, you can even restore a transactions log backup.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook