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
InProgramCodeNET Share test = E: est
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.