Backup verify in mssql server

 

 

SQL database backup verification methods can be used:

1)RESTORE VERIFYONLY command

– checks whether a SQL database backup can be read and restored To test a SQL database backup.

Command:- restore verifyonly from disk== ‘c:\msql.bak’

O/p =>The backup set on file 1 is valid.

2) To verify data structure and reliability in a SQL Server backup, the backup must be created using WITH CHECKSUMS (validates page checksums and generates a backup one). It Click  in Perform Checksum writing to media.

Command :- restore verifyonly   from disk= = ‘c:\msqlc.bak’   with Checksum

o/p :-The backup set on file 1 is valid.

Command :- restore verifyonly   from disk= = ‘c:\sales.bak”    with Checksum

o/p error:- Msg 3254, Level 16, State 1, Line 1
The volume on device ‘c:\sales.bak’ is empty.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

 

3)Headeronly:-

Result set containing all the backup header information for all backup sets on a particular backup device

Command :-  restore headeronly from disk=’c:\msqlc.bak’

Error:-Msg 3254, Level 16, State 1, Line 1
The volume on device ‘c:\sales.bak’ is empty.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

4)Filelistonly:-

result set containing a list of the database and log files contained in the backup set.

Command:-restore filelistonly from disk=‘c:\msqlc.bak’  

Restore filelistonly from disk =’D:\db_1.bak’ ,disk=’D:\db_2.bak’ ,disk=’db_3.bak’

 

Following query showing backup ,verify time and Shrink Time.

select percent_complete as [%],
command as [activiy],
Start_time as [Activity_start_time],
sd.name as [Database_name],
Dateadd(ms,estimated_completion_time/1000/60,GETDATE() ) as [Remaining_time],
(estimated_completion_time/1000) as [Remaining Time in Seconds]
from         Sys.dm_exec_requests SR
Inner join sys.databases SD      on SR.database_id=SD.database_id  where estimated_completion_time > 0

zz

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s