Using extended stored procedures in SQLServer to achieve remote backup and recovery of databases

Source: Internet
Author: User
In SQLServer, you can use extended stored procedures to implement remote database backup and recovery. For more information, see

SQL Server implements remote database backup and recovery through extended stored procedures. For more information, see

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.