March 28, 2009

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "Verifying and Restoring a Differential Backup using T-SQL In SQL Server"
  1. SQL recovery said...
    April 7, 2009 at 11:57 PM

    Good Article!!

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions