Backup verify in mssql server



SQL database backup verification methods can be used:


– 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.



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.


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], 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


Continue reading →

Dba world

Azure supported :-

1)sqlcmd utility

2)sql management studio

3)management portal for sql database

4)Data tier application

5)sql intergation services(SSIS)


Useful command for DBA

                                Command useful for MSSQL DBA :-
1) Ping :-
help in determining TCP/IP Networks IP address as well as determine issues with the network and assists in resolving them.

2) Telnet :-
The telnet commands allow you to communicate with a remote computer that is using the Telnet protocol. You can run telnet without parameters in order to enter the telnet context, indicated by the Telnet prompt (telnet>). From the Telnet prompt, use the following commands to manage a computer running Telnet Client.
         Example:- Telnet 1433  => check port is open in network
3)Tracert :-
     The TRACERT diagnostic utility determines the route taken to a destination by sending Internet Control Message Protocol (ICMP) echo packets with varying IP Time-To-Live (TTL) values to the destination. Each router along the path is required to decrement the TTL on a packet by at least 1 before forwarding it, so the TTL is effectively a hop count. When the TTL on a packet reaches 0, the router should send an ICMP Time Exceeded message back to the source computer.
         Example :- Tracert

NLTest can test and reset the secure channel established by the NetLogon service. This secure channel is established between clients and the domain controller that logs them on. NLTest does not work for clients using Kerberos for authentication since this secure channel is not used with Kerberos.

To check mostly in Cluster environment.
C:\Windows\system32>nltest /sc_query:company.local
Trusted DC Name \\DC01.domain.local
Trusted DC Connection Status Status = 0 0x0 NERR_Success
The command completed successfully

5)NET :-
You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. You can also perform these operation using command line in Windows. For this you must run command prompt with Administrative privileges.

To START SQL Server Service, use:
To STOP SQL Server Service, use:
6) SC:-
To START SQL Server Service, use:
To START SQL Server Service with parameters, use:
7) Netstat :-
 Netstat command to quickly see all the used and listening ports on your computer. Note it is not a complete substitute for a port scanning tool.
Example:- netstat -a | find “LISTENING”
You check port 1433 is listing which mark by red line.
8) IPconfig:-
Displays all current TCP/IP network configuration values and refreshes Dynamic Host Configuration Protocol (DHCP) and Domain Name System (DNS) settings. Used without parameters, ipconfig displays the IP address, subnet mask, and default gateway for all adapters.
9)Path :-
Displays or sets a search path for executable files.
10)SQCMD :-
SQLCMD invariably starts with examining command line options, i.e., its entry points.
ex:-Sqlcmd -S instancename -e => connect sql using windows authentication.