SQL Server implements remote database backup and recovery through extended stored procedures

This article explains how to extend the stored procedure of SQL Server database through examples to achieve remote backup and recovery.
Instance description:

Environment: win2k + sqlserver 2 K + query Analyzer
SQL SERVER service instance name: mainserver
Name of the database to be backed up: msdb
Local Machine name (Client): david
Local User: zf password 123
Local domain: domain
Folder for local backup: e: est

　　Step 1: create a shared folder

Call net share test = e: est in the program code (or in the CMD window ).

Or use netmask to add this API.

Brief description:

Net share: a windows internal network command.

Purpose: create a local shared resource to display the shared resource information of the current computer.

Syntax: see net share /?

　Step 2: Establish a shared credit relationship

Master.. xp_mongoshell 'net use \ david est 123/user: domainzf'

Brief description:

1: xp_mongoshell: an extended stored procedure of SQLSERVER.

To execute the specified command string in the command line interpreter of the operating system,

And return any output in text lines.

Syntax: see SQLSERVER online help

2: net use: a windows internal network command.

Function: connects or disconnects a computer from a shared resource, or displays information about the computer.

Connection information. This command also controls persistent network connections.

Syntax: see net use /?

　　Step 3: Back up the database

Backup database msdb to disk = '\ david estmsdb. Bak'

This does not need to be explained. For more information about the syntax, see SQL Server online help.

　　Step 4: delete Shared Folders

Call net share test/delete in the program code (or in the CMD window ).

Or use the netdomaindel API.

Result:

1376 pages have been processed. These pages belong to the database's 'msdbdata' file 'msdbdata' (located on file 1 ).

One page has been processed, which belongs to the database's 'msdblog' file (located on file 1 ).

The backup database operation successfully processed 1377 pages and took 3.653 seconds (3.086 MB/second ).

In this way, msdb on the mainserver is backed up to the E: estmsdb. bak file of the david machine. Is it easy to use? The same is true for database restoration. You only need to change the statement in step 3 to 'Restore database msdb from disk = '\ david estmsdb. Bak .. Can you try it after reading it ?! (The simplest test tool query analyzer + CMD window)

Note: The Extended Stored Procedure xp_cmdshell can only be called by SA-level users. It is also a security risk of SQL Server. Many DBAs like to delete or disable it, therefore, developers must be careful when using it.