Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve remote database backup and recovery through extended stored procedures in SQL Server. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The methods and steps of realizing remote backup and recovery are illustrated by examples:

Environment: win2k+sqlserver 2K+ query Analyzer

SQL SERVER service instance name: mainserver

Name of the database to be backed up: msdb

Local machine name (Client side): david

Local user: zf password: 123

Local domain name: domain

Folder that provides backup requirements locally: e: est

Step 1: set up a shared folder and call (or CMD window) net share test=e: est in the program code or use the API of NetShareAdd to briefly explain: net share: is a network command within WINDOWS. Function: to establish a local shared resource and display the shared resource information of the current computer. Syntax: see net share /?

Step 2: establish a shared credit relationship

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

Brief description:

1:xp_cmdshell: is an extended stored procedure for SQLSERVER.

Executes the given command string as an operating system command line interpreter and returns any output as a text line.

Syntax: see SQLSERVER online help

2:net use: is a network command within WINDOWS.

Function to connect or disconnect the computer from shared resources, or to display information about the computer connection. 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'

There is no need to explain this. See the SQLSERVER online help for syntax.

Step 4: delete the shared folder

Call (or CMD window) net share test / delete in the program code or use the API of NetShareDel

Result: 1376 pages have been processed, which belong to the database 'msdb' 's file' MSDBData' (on file 1). Processed 1 page that belongs to the database 'msdb' 's file' MSDBLog' (on file 1). The BACKUP DATABASE operation successfully processed 1377 pages and took 3.653 seconds (3.086 MB/ seconds). In this way, the msdb on the mainserver server is backed up to the E: estmsdb.bak file on the david machine, isn't it easy to use? The same is true for database recovery operations, as long as the statement in the third step is changed to 'restore database msdb from disk='\ david estmsdb.bak''. You can try it when you're done?! (the simplest test tool query Analyzer + CMD window)

Note: xp_cmdshell this extended stored procedure can only be called by SA-level users, and it is one of the security risks of SQL Server. Many DBA like to delete or disable it, so developers should be more careful when using it.

How to achieve remote database backup and recovery through extended stored procedures in SQL Server is shared here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.