Using SQLSERVER's extended stored procedures to implement remote backup and recovery

Using SQLSERVER's extended stored procedures to implement remote backup and recovery_PHP tutorial

WBOY

Release： 2016-07-13 17:02:16

Original

1051 people have browsed it

Recently, I am working on a data management module for the company's framework program (data application-oriented application system). The requirements of this module are relatively simple: backup, recovery and log cleaning. Our company's software basically uses C/S as the basic structure, so the two main functions of the data management module, 'backup and recovery', may be operated on the client side, and the files of backup and recovery may also be stored on the client side. end, so this data management module must be able to realize remote backup and recovery of the database.
Now that the premise of the article has been explained, it’s time to talk about how to implement it. In fact, it is very simple. I want to write a test example of remote backup
for everyone to see, so that it can be described clearly!
Example description:
Environment: win2k sqlserver 2K query analyzer
SQLSERVER service instance name: mainserver
Database name to be backed up: msdb
Local machine name (Client): david
Local user: zf Password: 123
Local domain name: domain
Local folder that provides backup requirements: e: est
Step 1: Create a shared folder
and call it in the program code (or CMD window) net share test=e: est
Or use the NetShareAdd API
Brief description:
net share: It is a network command inside WINDOWS.
Function: Create local shared resources and display 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 of SQLSERVER.
functions, executes the given command string in the form of the operating system command line interpreter,
and returns any output as a text line.
Syntax: See SQLSERVER online help
2:net use: It is a network command inside WINDOWS.
functions to connect or disconnect the computer from shared resources, or display information about the computer's
connection. This command also controls persistent network connections.
Syntax: See net use /?
Step 3: Back up database
backup database msdb to disk='david estmsdb.bak'
This does not require explanation. For syntax, see SQLSERVER online help
Step 4: Delete shared folder