I recently made data management modules for the company's framework program (with data-oriented applications). The demand for this module is relatively simple: backup, recovery, and cleanup logs. Our company's software is basically architecture with C / S, so two major functions in the data management module 'backup and recovery' may also be stored in the client, backup and recovery 'files in Client. End, thus this data management module must be able to implement remote backup and recovery databases.
The premise of the article is explained, so I should talk about how to implement it. In fact, it is very simple, I want to write a test instance for remote backup.
Let's see it, you can clearly describe it!
Example Description:
Environment: Win2K SQLServer 2K Query Analyzer
SQLServer Service instance name: mainserver
Requires backed up database name: MSDB
Local Machine Name (Client End): DAVID
Local users: ZF password: 123
Domain name: Domain
Locally provide folders for backup requirements: E: / TEST
Step 1: Establish a shared folder
Call (or CMD window) NET Share test = E: / TEST in program code
Or use netshareadd this API
brief introduction:
NET Share: It is a network command inside Windows.
Role: Establish a local shared resource to display the shared resource information of the current computer.
Grammar: See NET Share /?
Step 2: Establish a shared credit relationship
MASTER..XP_CMDSHELL 'NET USE / / DAVID / TEST 123 / USER: DOMAIN / ZF'
brief introduction:
1: XP_cmdshell: is the extended stored procedure of SQL Server.
Role, execute a given command string in the manner of operating the system command line interpreter,
And return any output in a text line.
Syntax: See SQLServer online help
2: NET USE: It is the network command inside Windows.
Role, connect your computer with shared resource or disconnect, or display about your computer
Connected information. This command also controls a persistent network connection.
Syntax: See NET USE /?
Step 3: Backup Database
Backup Database MSDB to Disk = '// David / Test / MSDB.BAK'
This doesn't need to be explained, the syntax will see SQLServer online help
Step 4: Delete shared folders
Call (or CMD window) NET Share test / delete in program code
Or use Netsharedl this API
result:
Page 1376 has been processed, these pages belong to the file 'msdbdata' (on file 1) of the file 'msdb'.
Process 1 page, these pages belong to the file 'msdblog' of the database 'msdb' (on the file 1).
The Backup Database operation has successfully handled 1377 pages and spent 3.653 seconds (3.086 MB / sec).
In this way, the MSDB on the mainserver server is backed up to the E: /TEST / TSDB.BAK file of the David machine, is it easy to use? The restoration database operation is also the same, as long as the statement of the third step is changed to 'restore database msdb from disk =' // david / test / msdb.bak 'is OK. . Can you try it? ! (The simplest test tool query analyzer cmd window) Note: XP_cmdshell This extended stored procedure can only call users, and is one of SQLServer's security hazards, many DBAs like to delete or disable them, so developers Be careful when using it.
The example in the article is just a brief explanation of how to use the extended stored procedure to achieve remote backup and recovery, without security and other considerations, I hope the reader is perfected in the code.
