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 to everyone, I can clearly describe it! Example: Environment: Win2k SQLServer 2K Query Analyzer SQLServer Service Instance Name: MainServer Requires Backup Database Name: MSDB Local Machine Name (Client End): David Local User: ZF Password: 123 Local Name: Domain provides backup requirements Folder: E: / TEST
Step 1: Establish a shared folder in program code (or CMD window) NET Share test = E: / TEST or brief description with NetShareAdd this API: NET Share: It is the network command inside Windows. Role: Establish 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 "NET Use // David / Test 123 / User: Domain / ZF 'Brief Description: 1: XP_cmdshell: is the extended stored procedure for SQL Server. Role, execute a given command string in the manner of the operating system command line interpreter, and returns any output in a text line mode. Syntax: See SQL Server Online Help 2: Net Use: It is the network command inside Windows. Role, connect your computer with a shared resource or disconnect, or display information about your computer connection. 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' does not need to explain, syntax see SQLServer online help
Step 4: Delete the Shared Folder Call (or CMD Window) NET Share Test / Delete or Use NetSharedl This API Result: Procedure 1376, these pages belong to the database 'msdb' file 'msdbdata' (in the file 1 on 1). 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)
Remarks: XP_cmdshell This extension stored procedure can only call users at level, and is one of SQLServer's security hazards. Many DBAs like to delete them or disable them, so developers should be careful when developers use 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.
