SQL Server extended storage process for remote backup and recovery

This article explains how to extend the storage process of the SQLServer database through examples to achieve remote backup and recovery ...... Instance Description: Environment: win2k + sqlserver2K + query analyzer SQLSERVER service instance name: mainserver database name to be backed up: msdb Local Machine name (Client Side): david local

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 database name to be backed up: msdb Local Machine name (Client Side): david local

This article resolvesSQL ServerDatabaseExtensionStorageProcess,ImplementationRemoteBackupAndRestoreMethods and steps ......

　　Instance description:

1. Environment: win2k + sqlserver 2 K + query Analyzer
2. SQL SERVER service instance name: mainserver
3. YesBackupDatabase Name: msdb
4. Local Machine name (Client): david
5. Local User: zf password 123
6. Local domain: domain
7. LocalBackupRequired Folder: 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_cmdshell: SQL ServerExtensionStorageProcess.

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:BackupDatabase

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, the msdb on the mainserver isBackupThe E: estmsdb. bak file on the david machine is ready for use. Is it easy to use?RestoreThe same is true for database operations. 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:Xp_cmdshellExtensionStorageProcessIt can only be called by SA-level users, and it is one of the security risks of SQL Server. Many DBAs like to delete or disable it, so developers should be careful when using it.