How to take MS SQL server DB backup on network mapped drive

How to take MS SQL server DB backup on network mapped drive::



EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO



The syntax of this command is:
NULL
NET USE
[devicename | *] [\\computername\sharename[\volume] [password | *]]
        [/USER:[domainname\]username]
        [/USER:[dotted domain name\]username]
        [/USER:[username@dotted domain name]
        [/SMARTCARD]
        [/SAVECRED]
        [[/DELETE] | [/PERSISTENT:{YES | NO}]]
NULL
NET USE {devicename | *} [password | *] /HOME
NULL
NET USE [/PERSISTENT:{YES | NO}]
NULL
NULL




EXEC XP_CMDSHELL 'net use H: \\xx.xx.xx.xx\bkp\ /user:<domain>\<username> <password> '

EXEC XP_CMDSHELL 'Dir H:'


Now you can able to see H: drive in the backup window.



To Delete the Mapped Drive::

EXEC XP_CMDSHELL 'net use H: /delete'  

I/O is frozen on database . No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Error: 

I/O is frozen on database <database>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

I/O was resumed on database <database>. No user action is required.

DB Version: MS SQL Server 2016 SP2.

Scenario:

Every day we have observed the above error in one of our production database. When I retrieved the backup history of a database, the specific backups are created every 6 hours of interval of time. At that time all the application jobs got suspended for time being. This was a daily complaint we received from application team. 

First, it starts with an I/O freeze message on all databases at the same time.

I/O is frozen on database <database>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Then, after 5 seconds I/O is resumed on all databases.I/O was resumed on database <database>. No user action is required.

This error was due to VEEAM backup. As our whole infra backup taken through VEEAM. This has been scheduled for every 6 hours and had removed the schedule during business hours.

MS SQL Server 2016, Log shipping stopped due to LSN chain break [TYPE=DISK: {'NUL'}]

In our production database have configured Log shipping. One day Log shipping stopped and found the below message in a log file.


Log was backed up. Database: model, creation date(time): 2003/04/08(09:13:36), first LSN: 42:3512:1, last LSN: 42:3632:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'NUL'}). This is an informational message only. No user action is required.


It was a strange phenomenon as "DISK: NUL found in error message. Since our whole infrastructure under VEEAM backup and after digging into it, I found that "VEEAM -Application-Aware Processing" feature, which creates transactionally consistent backups of MS SQL Server.

As soon as we disabled this feature and log shipping running smoothly.

Hope this will help :)

Error: 17836, Severity: 20, State: 17. Length specified in network packet payload did not match number of bytes read; the connection has been closed.

Logon Error: 17836, Severity: 20, State: 17. 

Message : 
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xx.xx.xx.xx]

Since the SQL Server has Event ID 17836 logged. It is more like an authentication issue.

The error message depicts that there is some process trying to connect at SQL port but it is not a valid SQL related activity.

Here are few more causes based on my search on the internet:

1. Network team doing Port Scanning

2. Sometimes Antivirus programs do check the port.

3. MSSQL Server is under a DDoS

In our case, I have captured and analyze incoming traffic & bad packets with "Wireshark" tool and "netstat -na" output and from these logs found "GFI-Languard" tool which scan, detect, assess, windows patches & security update/vulnerabilities on the server. 

Hope this will help :)