Remote backup and recovery using SQL Server's extended stored procedures

server|sqlserver| backup | Stored Procedures | Recovery recently I was working on a data management module for the company's framework program (data application-oriented applications), a simple requirement for this module: backup, recovery, and cleanup logs. My company's software basically to C/s as the basic framework, so the two main functions of the Data Management module, ' Backup and recovery ', can be operated on the client side, and backup and recovery files are also likely to be stored on the client side, so this data management module must be able to implement the remote backup and recovery database.

The premise of the article is finished, it should be said how to achieve it concretely. It's really simple, I want to write a test instance of a remote backup
For everyone to see, can be very clear description of it!
Example Description:
Environment: Win2k+sqlserver 2k+ Query Analyzer
SQL Server Service Instance name: Mainserver
Database name to be backed up: msdb
Local machine name (client side): David
Local User: ZF Password: 123
Local Domain name: domain
A folder for local backup requirements: e:\test

First step: Create a shared folder
Call (or CMD window) net share in program code Test=e:\test
Or use the Netshareadd API
Brief description:
NET share: is a network command within Windows.
Role: Create a local shared resource that displays shared resource information for the current computer.
Syntax: see net share/?
Step two: Establish a shared credit relationship
Master.. xp_cmdshell ' net use\\david\test123/user:domain\zf '
Brief description:
1:xp_cmdshell: is an extended stored procedure for SQL Server.
function, executing the given command string in the manner of the operating system command-line interpreter,
and returns any output as a text line.
Syntax: See SQL Server online Help
2:net use: is a network command within Windows.
To connect or disconnect a computer from a shared resource, or to display information about a computer
The information for the connection. This command also controls persistent network connections.
Syntax: see net use/?

Step three: Back up the database
Backup DATABASE msdb to disk= ' \\david\test\msdb.bak '
This doesn't need to be explained. syntax See SQL Server online Help

Fourth step: Delete the shared folder
Call (or CMD window) net share in program code Test/delete
Or use the Netsharedel API
Results:
Processed 1376 pages that belong to the file ' Msdbdata ' (located on file 1) of the database ' msdb '.
Processed 1 pages that belong to the file ' Msdblog ' (located on file 1) of the database ' msdb '.
The BACKUP DATABASE operation successfully processed 1377 pages and took 3.653 seconds (3.086 mb/sec).

So the mainserver on the server's msdb back to the David Machine's E:\test\msdb.bak file, easy to use it? The same is true with the restore database operation, as long as the statement of the third step is changed to ' Restore database msdb from disk= ' \\david\test\msdb.bak '. Can you try it after you finish?! (Simplest test Tool Query Analyzer +cmd window)

Note: xp_cmdshell this extended stored procedure can only be invoked by the SA-level user and is one of the security vulnerabilities of SQL Server, which many DBAs like to delete or disable, so be careful when using the developer.
The examples in this article are just a brief description of how the extended stored procedures should be used for remote backup and recovery, with no security and other considerations involved, and the need for readers to refine themselves in the code.