Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 14 July 2016

Database RESTORE WITH VERIFYONLY RESTORE WITH HEADERONLY RESTORE WITH FILELISTONLY BACKUP WITH DIFFERENT DRIVE in SQL Server

Database RESTORE WITH VERIFYONLY RESTORE WITH HEADERONLY RESTORE WITH FILELISTONLY BACKUP WITH DIFFERENT DRIVE in SQL Server


When you take backup command with checksum as see below




 After that you can check that Backupfile with checksum have valid or not



 then you can also view detail information as see below


Also if you had backup with checksum it also works both RESTORE HEADERONLY WITH CHECKSUM & RESTORE HEADERONLY without checksum as see below




 Please note the extra time taken for a restore verifyonly with checksum command.
 Another observation that may be noted is that a backup without a checksum and a backup with checksum  takes about the same time.

Also note that if you did nt use Backup Log with checksum then you cant able to use it on 
RESTORE WITH HEADERONLY checksum it will throw error 


After that when you mention in RESTORE WITH HEADONLY OPTION it will throw error as see below



 But if you are not use with checksum option then it will give result. Because you have to decide before specify you need to take backup with checksum then only you can use it on restore verifyonly option



These same strategy followed on backup  as see below
Database backup taken without specify checksum

if you did nt specify with checksum on restore it gives result as see below

    As If you specify with checksum it gives error see below





BACKUP FILE STORES WITH DIFFERENT DRIVE:

If you want to take backup on two locations you can specify as see below


BACKUP DATABASE TBK
TO DISK='E:/TBK_bk_Device1E.bak',Disk='F:/TBK_bk_Device2F.bak'


 it will save in E Drive and F Drive in local



 It saves same 92.5 MB size in both devices



 
 We can also see the mdf and ldf files using backup file as see below

Restore Filelistonly
FROM DISK='E:/TBK_bk_Device1E.bak'





We can also FIRST LSN,LAST LSN,Backp start date and finish date as see below


Restore headeronly
FROM DISK='E:/TBK_bk_Device1E.bak'





We have to verify Database backup is valid and it should be restorable or not using but you need to give all backup file as see below

Restore verifyonly
FROM DISK='E:/TBK_bk_Device1E.bak'-----------you should not give only 1 device location if you saved in 2 locations





So if you give 2 devices it works fine and tells you whether it is valid or not