SQL Server extended storage process for remote backup and recovery

This article resolves SQL ServerDatabase extended storage process, implementation Remote backup and recoveryMethods and steps ......

　　Instance description:

1. Environment: win2k + sqlserver 2 K + query Analyzer
2. SQL SERVER service instance name: mainserver
3. Name of the database to be backed up: msdb
4. Local Machine name (Client): david
5. Local User: zf password 123
6. Local domain: domain
7. 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:Xp_mongoshell is an extended stored procedure that can only be called by SA-level users. It is one of the security risks of SQL Server. Many DBAs like to delete or disable it. Therefore, developers must be careful when using it.